How to Define Table Relationships and Referential Integrity

Top  Previous  Next

Define relationships between tables in the Relationship Properties dialog. The relationships for the currently selected table appears in the Relations pad on the right bottom of the Quick View panel.

1.

Select a table from the DCT Explorer list.

2.

In the Quick View Relations pad, press the Add button.

The Relationship Properties dialog appears.

3.

Select the relationship Type from the drop down list: 1:Many or Many:1.

The label for the group box immediately below will change to Child or Parent, depending on your choice.

4.

Select the Related Table from the drop down list.

5.

Select Primary Key or Foreign Key for the first table from the drop down list at the top right of the dialog.

Clarion automatically changes the label for the drop down list (either Primary Key or Foreign Key) according to the relationship type.

6.

Select the Primary Key or Foreign Key for the related table, if applicable, from the drop down list immediately below the first drop down list.

7.

Press the Map by Name button to establish the link between the two keys by matching field names within the two keys, or press the Map by Order button to establish the link between the two keys by matching the fields by the order they appear within the two keys. Alternatively, you can map each field manually by double-clicking the field name in the Field Mapping list.

The Field Mapping lists show the actual links established between the two tables.

8.

Optionally set Referential Integrity Constraints by choosing from the On Update and On Delete drop down lists in the Referential Integrity Constraints group box.

See the section below for further information on Referential Integrity Constraints.

9.

Press the OK button.

 

Setting Referential Integrity Constraints

By setting referential integrity constraints in the data dictionary, you can instruct the Application Generator on how to set up executable code for linked field updates and deletions when working with related tables.

Referential Integrity requires that a foreign key must always have a match in the primary key. This raises potential problems when the end user wishes to change or delete the primary key record.

The New Relationship Properties dialog allows you to specify how the executable code should handle these situations when one of several related records is updated or deleted..

No Action

Instructs the Application Generator not to generate any code to maintain referential integrity.

Restrict

Tells the Application Generator to prevent the user from deleting or changing an entry, if the value is used in a foreign key. For example, if the user attempts to change a primary key value, the generated code checks for a related record with the same key value. If it finds a match, it will not allow the change.

Cascade

Tells the Application Generator to update or delete the foreign key record. For example, if the user changes a primary key value, the generated code changes any matching values in the foreign key. If the user deletes a parent record, the code deletes the children too.

Clear

Instructs the Application Generator to change the value in the foreign key to null or zero.

 

Next: An Alternative Way to do All of the Above