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 . 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:
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).
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:
|
|
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 . 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:
The same Foreign Key will be shown in the Database Builder's tree as a child node under the Table 'customers.addresses'.