ACC: Cannot Enforce Referential Integrity in Relationship
ID: Q112111
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SYMPTOMS
Novice: Requires knowledge of the user interface on single-user computers.
You cannot enforce referential integrity when you are defining a
relationship between tables.
CAUSE
There are several reasons why you may not be able to enforce referential
integrity when you define a relationship, including the following:
- Existing data in the tables violates referential integrity. For example,
you have a child record that does not have a matching parent in the
primary table.
- You can define a relationship with an attached table, but Microsoft
Access does not enforce referential integrity between two tables unless
both tables are in the same database and the database user has
permissions to create the relationship in that database.
- In Microsoft Access 2.0, you started dragging from the wrong table or
field. You must drag the primary key field (or uniquely indexed field)
from the primary table to the related table. For example, to enforce
referential integrity between the Customers and Orders tables in the
sample database NWIND.MDB, you must drag the Customer ID field (primary
key) from the Customers table to the Customer ID field in the Orders
table. If you did not start dragging from the primary table, click
Cancel in the Relationships dialog box and start again.
- You chose the correct fields, but they are not appropriately indexed.
Before adding relationships, open the primary table in Design view and
set a primary key or create a unique index for the fields you want to
use in an enforced relationship.
- Although you can create relationships using queries as well as tables,
referential integrity is not enforced with queries.
MORE INFORMATION
Referential integrity is a set of rules that preserves the defined
relationships between tables when you enter or delete records. If you
enforce referential integrity, you cannot add a record to a related table
when there is no associated record in the primary table, change a value in
a primary table that would result in an orphan record in a related table,
or delete a record from a primary table when there is a matching related
record.
If you select the Cascade Update Related Fields or Cascade Delete Related
Records option for a relationship, Microsoft Access will change or delete
related records to ensure that the referential integrity rules are enforced
when you change or delete records.
REFERENCES
For more information about referential integrity, type "referential
integrity" in the Office Assistant, click Search, and then click to view
"What is referential integrity."
Additional query words:
tables ri relationships
Keywords : kbusage RltRef
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb