How to Enforce Referential Integrity in Access 2003

In Microsoft Access 2003, referential integrity rules prevent the creation of orphan records in the table on the many side of the one-to-many relationship. Referential integrity between tables is enforced by default when you create a relationship in your database diagram. While Access enforces this for the user by default, the user can still toggle it on and off. Here are some steps on how to enforce referential integrity in Access 2003.

Things You'll Need

• Computer
• Access 2003

Instructions

1. On the "Database" toolbar, click the "Relationships" button to open the "Relationships" window.

2. Click the "Show Table" button if the "Show Table" dialog box does not show on the toolbar. Then double-click "Categories" and "Products" in the list displayed.

3. Close the "Show Table" dialog box in order to view the "Relationships" window. Click "CategoryID" in the first table, and drag it on top of "CategoryID" in the second table.

4. Select the "Enforce Referential Integrity" check box, select the other two check boxes, and then click "Create." Access 2003 will display the "Edit Relationships" dialog box, which lists the fields you have chosen to relate.

Tips & Warnings

• You can change the conditions under which referential integrity is enforced by editing the relationship's properties.

• You can set Referential Integrity between two tables in Microsoft Access if 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).

• Referential integrity is not set between two tables if they are not in the same Microsoft Access 2003 database, the matching field is not a "Primary Key" in one table or does not have a unique index, or the related fields have different data.