Defining referential integrity

Topic group Related topics

You can establish referential integrity between tables in the current database. If no database is specified, you can establish referential integrity between tables in the current directory.

To define a referential integrity relationship,

1. In the Navigator, Tables page, use the Look In box to select a database alias or a directory containing tables (such as .DBF or .DB type) that support referential integrity.

• Choose File|Database Administration. The Database Administration dialog box appears (To see SQL databases listed in this dialog box, you must first have given them a BDE alias.)

• Specify a Table Type that supports referential integrity, such as dBASE or Paradox, then click Referential Integrity. The Referential Integrity Rules dialog box appears

• Choose New. The New Referential Integrity Rule dialog box appears. All tables in the current database or directory appear in the Parent Table and Child Table drop-down lists

• Choose a parent table from the Parent Table list. The table’s key fields appear in the Primary Key Fields area of the dialog box.

• Choose the child table from the Child Table list. Fields available for referential integrity appear in the Available Child Fields list.

• Specify whether the tables are in a one-to-one or one-to-many relationship in the Relationship panel. The relationship you choose changes the available child fields.

One-to-one relationships can be defined between the primary key field in the parent and the primary key field in the child, or any field in the child that has a unique index.

One-to-many relationships can be defined between an indexed field that is not the primary key in the child and the primary key field in the parent.

• Choose the child table’s field in the Available Child Fields list and click the Add Field arrow. The field name appears in the Related Child Fields area of the References panel.

You can establish referential integrity with a complex (or composite) key. If the parent table has a complex key, add fields from the Fields list to match all of the fields in the parent’s key.

• Select the update and delete behavior you want (see below).

• Optionally change the rule name dBASE Plus provides in the topmost box.

• Choose OK to save the referential integrity relationship.

Note

If you attempt to define referential integrity on a table that already contains data, some existing values may not match a value in the parent’s key field. When this happens, the operation fails and you receive an error message.

Update and delete behavior

You can specify the following rules for updating and deleting data in a parent table that has dependent rows in a child table:

Restrict: You cannot change or delete a value in the parent’s key if there are rows that match the value in the child table.

For example, if the value 1356 exists in the Customer No field of Orders, you cannot change that value in the Customer No field of Customer. (You can change it in Customer only if you first delete or change all rows in Orders that contain it). If, however, the value doesn’t exist in any rows of the child table, you can change the parent table.

Cascade: Any change you make to the value in the key of the parent table is automatically made in the child table. If you delete a value in the key of the parent table, dependent rows in the child table are also deleted.

The availability of cascading updates and deletes varies according to the table type:

dBASE Level 7: Cascading updates or deletes permitted

Paradox: Cascading updates only

Oracle: Cascading deletes only

Sybase: No cascading updates or deletes permitted

InterBase: No cascading updates or deletes permitted

Microsoft SQL Server: No cascading updates or deletes permitted