Suppose I've chosen referential integrity when setting up a relationship, but get an error message - why?
Submitted by: MuhammadThere are two common reasons for this:
1 - Existing data violates integrity (error message: "Microsoft Access can't create this relationship and enforce referential integrity" - this message is then followed by a nice clear example of what the problem might be).
The most likely reason that either or both of your tables already have data in them but that the data violates the integrity rule.
Example A: You have data in your membership table but not yet in your category table. The tables will be joined via the category number. As referential integrity requires that all category numbers in the membership table are also in the category table, you must now set data in your category table before creating the relationship.
Example B: You have data in both the membership and the category tables. The likely reason is that you have a category number in your membership table that is not in the category table. Correct your data and try again.
It is safest to set up all validations and rules before entering any data.
2 - Key data types do not match (error message: "relationship should be on the same number of fields with the same data type")
The relationship joins two keys - one in each table. These keys should be of the same data type and size. If the primary key of one table is an Autonumber field (LongInteger), then the corresponding foreign key in the other table should be a Number data type, also LongInteger. If one key is a text field, then so should the other one (and of the same size).
Submitted by: Muhammad
1 - Existing data violates integrity (error message: "Microsoft Access can't create this relationship and enforce referential integrity" - this message is then followed by a nice clear example of what the problem might be).
The most likely reason that either or both of your tables already have data in them but that the data violates the integrity rule.
Example A: You have data in your membership table but not yet in your category table. The tables will be joined via the category number. As referential integrity requires that all category numbers in the membership table are also in the category table, you must now set data in your category table before creating the relationship.
Example B: You have data in both the membership and the category tables. The likely reason is that you have a category number in your membership table that is not in the category table. Correct your data and try again.
It is safest to set up all validations and rules before entering any data.
2 - Key data types do not match (error message: "relationship should be on the same number of fields with the same data type")
The relationship joins two keys - one in each table. These keys should be of the same data type and size. If the primary key of one table is an Autonumber field (LongInteger), then the corresponding foreign key in the other table should be a Number data type, also LongInteger. If one key is a text field, then so should the other one (and of the same size).
Submitted by: Muhammad
Read Online Microsoft Access Developer Job Interview Questions And Answers
Top Microsoft Access Developer Questions
☺ | Tell us what property does every object in MS Access have? |
☺ | What are managing Data Tasks? |
☺ | Tell us how Do You Create An Append Query? |
☺ | Tell us About The Various Features Present In Ms Access? |
☺ | Tell us what format should my database be saved in to allow different versions of Microsoft Access to open the database? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |