databasedev.co.uk - database solutions and downloads for microsoft access

Enforce Referential Integrity

Enforcing Referential Integrity for a relationship in a Microsoft Access database can avoid the loss or inadvertent updating of data records.

Referential Integrity Enforcement Guidelines:

You can set Referential Integrity between two tables in Microsoft Access if the following are true -

• Both of the tables are in the same Microsoft Access database.
• The matching field is a Primary Key in one table or has a unique index.
• The related fields have the same data type (the exception is that an AutoNumber field can be related to a Number data type with a field size of Long Integer).

When database tables are linked together, one table is usually called the Parent table and the other (the table that it is linked to) is usually called the Child. This is known as a parent-child relationship between Microsoft Access tables. Referential Integrity guarantees that there will never be an orphan, a child record without a parent record.

Referential Integrity operates strictly on the basis of the tables key fields; it checks each time a key field, whether primary or foreign, is added, changed or deleted. If a change to a key creates an invalid relationship, it is said to violate referential integrity.

The Effects on Data Modification:

When referential integrity is enforced (without enabling the cascading options), certain rules apply to the data. The following list gives some examples of this -

• You can't enter a value in the Foreign Key field of one table if there is not a matching value in the Primary Key of the related table.
• You can't delete a record from the Primary table (the table in which the primary key is the related field) if a matching record exists in the related table.
• You can't change the value in the Primary Key of the primary table if there are related records in the related table.

Good reasons for Enforcing Referential Integrity:

A CustomerID AutoNumber field in the Customers table is a unique Primary Key and can be related to a numeric field in the Orders table in a One-To-Many relationship.

You would not want to allow a user to enter any Order data for a Customer that does not have a record in the Customers table. Nor would you want to allow a user to alter the CustomerID field for a record in the Customer table, as this would break the link with related Order data for that Customer.

Deleting a Customer record that has matching records in the Orders table would also not be allowed.