Microsoft Access 2007: Enforce Referential Integrity


Archived Historical Data: Not Actively Maintained.


The purpose of using referential integrity is to to prevent orphan records and to keep references synchronized so that you don't have any records that reference other records that no longer exist. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that would violate referential integrity for that table relationship.   For more information on Referential Integrity, click HERE.

 

To have Access propagate referential updates and deletions so that all related rows are changed accordingly, see the section 'Set the cascade options' below.

1.  Click the Microsoft Office Button | Open.

Screen shot of step one



2.  In the Open dialog box, select and open the database.

Screen shot of step two


3.  On the Database Tools tab, Select Show/Hide |  Relationships. The Relationships window appears.


If you have not yet defined any relationships and this is the first time you are opening the Relationships window, the Show Table dialog box appears. If the dialog box appears, click Close.

1.  On the Design tab, select Relationships | All Relationships.

Screen shot of step one


All tables that have relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

1.  Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

2.  Double-click the relationship line or on the Design tab, select Tools | Edit Relationships. The Edit Relationships dialog box appears.

3.  Select the Enforce Referential Integrity check box.

Screen shot of step three


4.  Make any additional changes to the relationship, and then click OK.


After you have enforced referential integrity, the following rules apply:

  • You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key field of the primary table — doing so creates orphan records.
  • You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the Cascade Delete Related Records check box.
  • You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that Order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.

If you have difficulty enabling referential integrity, note that the following conditions are required in order to enforce referential integrity:

  • The common field from the primary table must be a primary key or have a unique index.
  • The common fields must have the same data type. The one exception is that an AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer.
  • Both tables must exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.

Set the cascade options

You might encounter a situation in which you have a valid need to change the value on the "one" side of a relationship. In such a case, you need Access to automatically update all of the effected rows as part of a single operation. That way, the update is completed in full so that your database is not left in an inconsistent state — with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.

You might also need to delete a row and all related records — for instance, a shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and choose the Cascade Delete Related Records option, Access automatically deletes all records that reference the primary key when you delete the record that contains the primary key.
 

Turn cascade update and/or cascade delete on or off

1.  Click the Microsoft Office Button | Open.

Screen shot of step one


2.  In the Open dialog box, select and open the database.

Screen shot of step two


3.  On the Database Tools tab, select Show/Hide |  Relationships.

The Relationships window appears.


If you have not yet defined any relationships and this is the first time you are opening the Relationships window, the Show Table dialog box appears. If the dialog box appears, click Close.

1.  On the Design tab, select Relationships | All Relationships.

Screen shot of step one


All tables that have relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

1.  Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

2.  Double-click the relationship line or  On the Design tab, select Tools | Edit Relationships. The Edit Relationships dialog box appears.

3.  Select the Enforce Referential Integrity check box.

4.  Select either the Cascade Update Related Fields or Cascade Delete Related Records check box, or select both

Screen shot of step four


5.  Make any additional changes to the relationship, and then click OK.

 Note:   If the primary key is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.


Referenced from: Microsoft.com



4604  
4/1/2013 10:29:58 AM  

We love feedback! Please help us improve this article.


Article Rating:
Email Address:
(Optional, unless you would like to hear back from us)
Comments:
GROK is a resource of Louisiana State University developed and maintained with support of the LSU Student Technology Fee.  We love getting feedback from the general public, but our one on one support efforts are generally dedicated to the LSU community.  Thanks for your understanding!
"" ""

Information Technology Services
200 Frey Computing Center · Baton Rouge, LA 70803
Telephone: 225-578-3700 · Fax: 225-578-3709 · E-mail: helpdesk@lsu.edu

Copyright © 2006. All Rights Reserved. Official Webpage of Louisiana State University.