Referential CONSTRAINT Definition (referential_constraint_definition)

A referential CONSTRAINT definition (referential_constraint_definition) defines an integrity condition that must be satisfied by all the rows in two tables. The resultant dependency between two tables affects changes to the rows contained in them.

Structure

Syntax Syntax

<referential_constraint_definition> ::=
  FOREIGN KEY [<referential_constraint_name>]
    (<referencing_column>,...)
    REFERENCES <referenced_table>
    [(<referenced_column>,...)]
    [<delete_rule>]

<referencing_column> ::=
  <column_name>

<referenced_table> ::=
  <table_name>

<referenced_column> ::=
  <column_name>

End of the code.

Referential CONSTRAINT Definition
referencing_column	Referencing column (column that establishes the link to the column that is to be addressed)
referenced_table referenced_column	Referenced table/referenced column (table/column that is to be addressed)

Examples

SQL Tutorial, Foreign Key Dependencies Between Tables

Explanation

A referential CONSTRAINT definition can be used in a CREATE TABLE statement or ALTER TABLE statement (ADD definition). The table (table_name) specified in a corresponding statement is referred to in the following sections as the referencing table.

Name of a Referential Constraint (referential_constraint_name)

The name of a referential constraint can be specified after the keywords FOREIGN KEY.

If the name of a referential constraint is specified, it must be different from all other names of referential constraints for the referencing table.
If no referential constraint name is specified, the database system assigns a unique name (based on the referencing table).

Referencing Columns (referencing_column)

The referencing columns (referencing_column ) are specified in the referential CONSTRAINT definition. The referencing columns must denote columns in the referencing table and must all be different. They are also called foreign key columns.

Referenced Columns (referenced_column)

If no referencing columns are specified, the result is the same as if the key columns in the referenced table (referenced_table) were specified in the defined sequence.
If referenced columns are specified that are not the key in the referencing table, the referenced table must have a UNIQUE definition whose column names and sequence match those of the referenced columns.

Relationship Between Referenced and Referencing Columns:

The number of referenced columns must be equal to the number of referencing columns.
The nth referencing column corresponds to the nth referenced column.
The data type and the length of each referencing column must match the data type and length of the corresponding referenced column.

The referencing table and the referenced table must be base tables, but not temporary base tables.

The current user must have the ALTER privilege for the referencing table and the REFERENCE privilege for the referenced table.

Inserting and Modifying Rows in the Referenced Table

The following restrictions apply when rows in the referencing table are added or modified:

Let Z be an inserted or modified row. Rows can only be inserted or modified if one of the following conditions is fulfilled for each associated referenced table (referenced_table):

Z is a matching row.
Z contains a NULL value in one of the referencing columns (referencing_column).
The referential CONSTRAINT definition defines the DELETE rule ON DELETE SET DEFAULT, and Z contains the DEFAULT value in each referencing column.

When an INSERT or UPDATE statement is applied to a referencing table, the database uses a blocking behavior for the referenced table that corresponds to isolation level 1, irrespective of the isolation level defined for the current session. When a DELETE statement is applied to a referenced table, the database system uses a locking behavior that corresponds to isolation level 3.

Matching Row

A row in the referencing table is called a matching row of a row in the referenced table (referenced_table) if the values of the corresponding referencing columns (referencing_column) and referenced columns (referenced_column) are identical.

A referential CONSTRAINT definition defines a 1:n relationship between two tables. This means that more than one matching row can exist for each row in the referenced table.

A row in the referenced table in a referenced column cannot be changed if at least one matching row exists.

CASCADE Dependency

A table T* is CASCADE dependent on table T if a series of referential CONSTRAINT definitions R1, R2, ..., Rn (n>=1) exist where:

T* is the referencing table of R1

T is the referenced table (referenced_table) of Rn

All referential CONSTRAINT definitions use CASCADE.

For i=1,...,n-1, n>1 is the referenced table of Ri is equal to the referencing table of Ri+1

Let R1 and R2 be two different referential CONSTRAINT definitions with the same referencing table S. T1 denotes the referenced table of R1, T2 denotes the referenced table of R2.

If T1 and T2 are identical, or if a table T exists so that T1 and T2 are CASCADE dependent on T, then R1 and R2 must both specify either CASCADE or RESTRICT.

Example Example

There are two different sequences of referential CONSTRAINT definitions that link the tables S and T. A DELETE statement on table T results in an action in table S. In order to ensure that the result of the DELETE statement does not depend on which of the two sequences of referential CONSTRAINT definitions is processed, the above restriction was selected for R1 and R2.

End of the example.

Reference Cycle

A reference cycle is a sequence of referential CONSTRAINT definitions R1, R2, ..., Rn where n>1, to which the following conditions apply:

For i=1, ..., n-1 the referenced table (referenced_table) of Ri is equal to the referencing table of Ri+1.
The referenced table of Rn is the referencing table of R1.

A reference cycle in which all of the referential CONSTRAINT definitions specify CASCADE is not allowed.

A reference cycle in which one referential CONSTRAINT definition does not specify CASCADE and all other referential CONSTRAINT definitions specify CASCADE is not allowed.

Self-Referencing CONSTRAINT Definition

A referential CONSTRAINT definition is self-referencing if the referenced (referenced_table) and referencing tables are identical.

With self-referencing referential CONSTRAINT definitions, the order in which a DELETE statement is processed can be important.

CASCADE specified: All of the rows affected by the DELETE statement are first deleted irrespective of the referential CONSTRAINT conditions. All matching rows in the rows that have just been deleted are then also deleted. As a result, all of the matching rows in the previous deletion operation are deleted, etc.

SET NULL or SET DEFAULT specified: All of the rows affected by the DELETE statement are first deleted irrespective of the referential CONSTRAINT conditions. Following this, SET NULL or SET DEFAULT is applied to the matching row.

More Information

Privileges: Overview

Ensuring Data Integrity