Book a Demo
Prev Next

Foreign Keys

A Foreign Key defines a column (or a collection of columns) that enforces a relationship between two Tables. It is the responsibility of the database server to enforce this relationship to ensure data integrity. The model definition of a Foreign Key consists of a parent (primary) Table containing a unique set of data that is then referred to in a child (foreign) Table.

In Enterprise Architect, a Foreign Key is modeled with two different (but related) UML components:

  • A Foreign Key constraint (a UML operation with the stereotype of <<FK>>) stored on the child Table and
  • An Association connector (stereotype of <<FK>>) defining the relationship between the two Tables

Create a Foreign Key

Although the definition of a Foreign Key can be complex, the Foreign Key Constraint screen simplifies the modeling of Foreign Keys. This screen is purpose-designed to help you select which constraint in the parent Table to use, and will automatically match the child Table columns to those in the parent Table that are part of the constraint. Different aspects of the process of developing a Foreign Key are described here separately for illustration, but the overall process should be a smooth transition.

A number of conditions must be met before a Foreign Key definition can be saved:

  • Both Tables must have matching DBMSs defined
  • The parent Table must have at least one column
  • The parent Table must have a Primary Key, unique constraint or unique index defined

Create a Foreign Key - using the Database Builder

Step

Action

See also

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.

Create a Foreign Key - using a relationship on a diagram

Step

Action

See also

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.

DDL Name Templates

The Foreign Key Constraint Screen

As an example this image shows the Foreign Key Constraint screen loaded with the details of 'fk_customersaddresses_customers' (as defined in the Example model).

Showing an example foreign key constraint in Sparx Systems Enterprise Architect.

Option

Action

See also

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.

DDL Name Templates

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.

DDL Name Templates

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.

Examples

This example shows simple Foreign Keys in a diagram:

A physical data model using Information Engineering connectors between tables, in Sparx Systems Enterprise Architect.

The same Foreign Key will be shown in the Database Builder's tree as a child node under the Table 'customers.addresses'.

Showing a foreign key in the Database Builder tree in Sparx Systems Enterprise Architect.

Learn more