Microsoft Access Developer Interview Question:

Suppose I've chosen referential integrity when setting up a relationship, but get an error message - why?

Submitted by: Muhammad

There 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