Defining a Database Using SQL › Defining a Referential Constraint

Defining a Referential Constraint

You create a referential constraint by issuing the CREATE CONSTRAINT statement and specifying the referenced and referencing tables and columns.

Things You Can Specify

1. Name of the constraint
2. The referencing table and column(s)
3. The referenced table and column(s)
4. Whether the referential constraint is linked or unlinked (the default)
5. Options such as clustered (ORDER BY) or indexed (INDEX)

Considerations

• The referenced column values of each row in the referenced table must be unique in the database. Therefore, ensure that either a unique CALC key or a unique index key is defined on the referenced columns.
• The datatype of a referencing column must be the same as its referenced column.
• If you specify an unlinked referential constraint:
  • The referencing table must have a CALC key or index defined on the referencing columns.
  • The order of the columns must be the same as the unique CALC key or index on the referenced columns.
  • If using an index on the referencing columns, the index can contain columns in addition to the referencing columns. The referencing columns must precede any additional columns in the index key.
• If you are defining a self-referencing constraint, it must be unlinked.
• Referential constraints (linked and unlinked) may not cross page group boundaries, meaning that the areas in which the referenced and referencing tables reside must have the same page group.

Example - Linked Referential Constraint

In this example, a linked referential constraint has been created to ensure 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);

Example - Unlinked Referential Constraint

In this example, an unlinked referential constraint has been created to ensure that an employee's manager is a valid employee. Since this is a self-referencing constraint (both columns being in the same table), it must be unlinked. UNLINKED is the default.

create constraint manager_emp
   employee (manager_id)
   references employee(emp_id);