Foreign Keys

1

In the Database Builder tree, right-click on the child Table name and click on 'Add new Foreign Key on <table name>'.

A dialog displays listing all the possible parent Tables.

2

Double-click on the required parent Table name in the list or select it and click on the OK button.

The 'Foreign Key Constraint' screen displays.

1

In the Data Modeling diagram, locate the required child (Foreign Key) Table and parent (Primary Key) Table.

2

Select an Association connector in the 'Data Modeling' page of the Diagram Toolbox.

3

Click on the child Table and draw the connector to the parent Table.

4

If the Foreign Key Constraint screen has been set to display automatically when two Tables are joined, it displays now. Otherwise, either:

• Double-click on the connector or
• Right-click on the connector and select the 'Foreign Keys' option

The Foreign Key Constraint screen displays.

Join on Constraint

This combo box lists all defined constraints in the parent Table that could be used as the basis of a Foreign Key. (These constraints can be Primary Keys, Unique Constraints or Unique Indexes.)

The first constraint in the list is selected by default; if this is not the constraint you want, select the correct constraint from the combo box.

When you select the constraint, its columns are automatically listed in the 'Involved Columns' panel, under the 'Parent: <tablename>' column.

Involved Columns

This list is divided into two: the columns involved in the selected constraint are listed on the left, and the child columns that are going to be paired to the parent columns are listed on the right.

When a constraint is selected (in the 'Join on constraint' field) the parent side is refreshed to display all columns assigned to the selected constraint. On the child side the system will automatically attempt to match each parent column to one of the same name in the child Table.  If the child Table does not have a column of the same name, a new column of that name will be added to the list, flagged with (*) to indicate that a new column will be created in the Table.

However, if you want to force the pairing to an existing child Table column or a new column with a different name, click on the column name field and either:

• Type in the replacement name, or
• Select an existing column (click on the drop-down arrow and select the name from the list)

Name

This field defines the name of the Foreign Key constraint, and defaults to a name constructed by the Foreign Key Name Template.

To change the name to something other than the default, simply overtype the value.

On Delete

Select the action that should be taken on the data in the child Table when data in the parent is deleted, so as to maintain referential integrity.

On Update

Select the action that should be taken on the data in the child Table when data in the parent is updated, so as to maintain referential integrity.

Parent

Click on the drop-down arrow and select the cardinality of the parent Table in the Foreign Key.

Child

Click on the drop-down arrow and select the cardinality of the child Table in the Foreign Key.

Create?

If you want to create a Foreign Key Index at the same time as the Foreign Key, set this property to True.

The name of the Foreign Key Index is controlled by the Foreign Key Index template, and the generated name is shown in the 'Name' field underneath the 'Create?' field.

Automatically show this screen when tables are joined

(For diagrammatic modeling) Select this checkbox to automatically display this screen whenever an Association is created between two Tables.

Delete

Click on this button to delete the currently selected existing (saved) Foreign Key.

A prompt is displayed to confirm the deletion (and the deletion of the Foreign Key Index, if one exists) - click on the Yes button.

Deleting a Foreign Key leaves an Association connector in place, which you can either edit or delete (right-click and select 'Delete association: to <Table name>').

OK

Click on this button to save the Foreign Key.