Managing foreign keys


Foreign keys are used to establish and maintain relational links in the database. They are automatically generated, based on the associations and their connectivity.

The foreign keys represent the relationship between tables. Each child table contains a copy of its parent table’s primary key. This copy is called foreign key.

Foreign keys are managed off-line, which means that the user has the responsibility of maintaining the referential integrity of the data model.


Generating foreign keys

First, the data model you have created has to be highlighted in the explorer window, then perform the following steps:
  1. Select Tools > Foreign keys > Generate foreign keys. The generate foreign keys window will appear.

  2. Choose the needed options (for information on the options, see next page).



  3. Confirm with the Generate Foreign Keys button to perform the operation. A report will appear at the end of the operation.

Modifications to NULL/NOT NULL possible will be applied automatically if they were needed to validate the model according to multiplicity. However, it will not be reported.

Select the Report button if you want to see the report before the generation. Remember, it will not procede to the foreign key generation.

NOTE: For Informix data models ONLY, the foreign columns refering to primary or unique key columns of type SERIAL or SERIAL8 will be set as INTEGER or INT8.


Generate foreign keys: options

Several options are offered during the generation process to control the actions applied to the existing foreign key columns.

Orphan columns:
A column is said to be an orphan when it is indicated as being foreign and the referenced primary or unique key column is not present in the model.


Delete orphan columns
  • Orphan foreign columns will be deleted when generating foreign keys.


  • Keep orphan columns as they are
  • Orphan foreign columns will be kept when generating foreign keys.


  • Set orphan columns as basic columns
  • Orphan foreign columns’ “foreign indicators” will be set to false when generating foreign keys.



  • Updating descriptors

    It is quite common to have foreign column properties differ from referenced primary or unique key columns. The list of options of descriptors update offers the possibility to propagate primary or unique key columns information to their corresponding foreign columns.

    Here are the five different descriptors:

    After the foreign key generation, your model should look similar to the following:



    Example of a foreign key generation report:




    Delete foreign keys

    This feature deletes foreign key constraints and columns for the whole data model.


    Propagate to foreign key columns

    Propagate primary or unique key columns' attributes to corresponding foreign key columns for the entire data model or a selection of columns (available on the column concept).


    Impact of association role properties

    Some of the properties of the association role have an impact on the foreign key management.

    Navigability

    It is applicable to the child end of the association. The association role has to be navigable for a foreign key to be generated. Apply on links between objects by selecting its value indicator (red square here).




    Constraint types
    Referenced constraint

    It is applicable only on primary or unique keys. The referenced constraint identifies a specific primary or unique key. If however, it returns an unspecified result, by default, the application will assume a reference to the primary key.