Enforce referential integrity between tables (ADP)

 Note   The information in this topic applies only to a Microsoft Access project (.adp).

Referential integrity between tables is enforced by default when you create a relationship in your database diagram. An enforced relationship ensures each value entered in a foreign key column matches an existing value in the related primary key column.

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

ShowChange referential integrity options for a new relationship

  1. In the Database window, click Database Diagrams Button image under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
  2. In your database diagram, create a relationship.

ShowHow?

You create a relationship between two tables when you want to associate rows of one table with rows of another.

ShowCreate a relationship in a database diagram

  1. In the Database window, click Database Diagrams Button image under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
  2. In your database diagram, click the row selector Row selector icon for the database column or combination of columns that you want to relate to a column in another table.
  3. While the pointer is positioned over the row selector, click and drag to the related table.
  4. Release the mouse button. The Create Relationship dialog box appears and attempts to match the columns you selected with columns of the same name and data type in the related table.
  5. In the Create Relationship dialog box, confirm that the columns you want to relate are shown in the Primary key table and Foreign key table lists.
  6. Choose OK to create the relationship.
On the diagram, the primary key side of the relationship is denoted by a key Key icon symbol. In one-to-one relationships, the table that initiated the relationship determines the primary key side. For example, if you create a relationship from the pub_id column in the publishers table to the pub_id column in the pub_info table, then the publishers table is on the primary key side of the relationship.

ShowCreate a relationship in Table Designer

  1. Click Tables Button image under Objects, click the table that is on the foreign key side of the relationship, and then click Design on the Database window toolbar.
  2. Right-click in the Table Designer and choose Relationships.
  3. Click the New button.
  4. From the drop-down list in Primary Key Table, choose the table that will be on the primary-key side of the relationship. In the grid beneath, enter the columns contributing to the table’s primary key. In the adjacent grid cell to the left of each column, enter the corresponding foreign-key column of the foreign-key table.
    The table designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.
  5. Choose Close to create the relationship.
  1. In the Create Relationship dialog box, clear or select one or more of the options. For more information about each option, click Help.

ShowChange referential integrity options for an existing relationship

  1. In the Database window, click Database Diagrams Button image under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
  2. In your database diagram, select the relationship line.
  3. Right-click the relationship line and select Properties.
  4. Choose the Relationships tab.
  5. Select the relationship from the Selected relationship list.
  6. Clear or select one or more of the options. For more information about each option, click Help.

The relationship is updated in the database when you save the database diagram or either of the related tables.

 
 
Applies to:
Access 2003