Modifying Indexes, CALC Keys, and Referential Constraints › Maintaining Referential Constraints › Creating a Referential Constraint

Creating a Referential Constraint

To create an unlinked or linked referential constraint, use the SQL DDL CREATE CONSTRAINT statement. CA IDMS/DB checks and rejects any invalid CREATE CONSTRAINT statements.

Considerations

• To create a linked constraint if both tables are not empty, you must drop and recreate the tables, defining the linked constraint before reloading the data.

  Note: For steps and considerations involved with this process, see Chapter 30, “Modifying Schema, View, Table, and Routine Definitions.

• When adding an unlinked constraint on a non-empty table, CA IDMS/DB ensures that all rows of the table satisfy the constraint. If one or more rows do not satisfy the constraint, the create will not be allowed.

Example

In the following example, a linked referential constraint has been created to make sure that the employee ID in the benefits table is a valid ID by checking it against the employee IDs in the employee table. The referential constraint is indexed and ordered by the fiscal year.

create constraint emp_benefits
   benefits (emp_id)
   references employee (emp_id)
   linked index
     order by (fiscal_year desc);