referential-constraint

CONSTRAINT constraint-name
Names the constraint. A constraint-name must not identify a constraint that was previously specified in the CREATE TABLE statement and must not identify a constraint that already exists at the current server.

If the clause is not specified, a unique constraint name is generated by the database manager.

FOREIGN KEY
Each specification of the FOREIGN KEY clause defines a referential constraint. FOREIGN KEY is not allowed if the table is a partitioned table.
(column-name,...)
The foreign key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB or DATALINK column. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allow nulls.
REFERENCES table-name
The table-name specified in a REFERENCES clause must identify the table being created or a base table that already exists at the application server, but it must not identify a catalog table, a global temporary table, a partitioned table, or a distributed table.

A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of a previously specified referential constraint. Duplicate referential constraints are allowed, but not recommended.

Let T2 denote the identified parent table and let T1 denote the table being created.

The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the description of the nth column of that parent key must have identical data types, lengths, and CCSIDs.

(column-name,...)
The parent key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than once. The column must not be a LOB or DATALINK column. The number of identified columns must not exceed 120, and the sum of their byte counts must not exceed 32766-n, where n is the number of columns specified that allow nulls. For information on byte-counts see Table 52.

The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names need not be specified in the same order as in the primary key; however, they must be specified in corresponding order to the list of columns in the foreign key clause. If a column name list is not specified, then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.

The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.

ON DELETE
Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are five possible actions:
  • NO ACTION (default)
  • RESTRICT
  • CASCADE
  • SET NULL
  • SET DEFAULT

SET NULL must not be specified unless some column of the foreign key allows null values.

CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.

The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.

  • If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
  • If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
  • If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
  • If SET DEFAULT is specified, each column of the foreign key of each dependent of p in T1 is set to its default value.
ON UPDATE
Specifies what action is to take place on the dependent tables when a row of the parent table is updated.

The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.

  • If RESTRICT or NO ACTION is specified, an error occurs and no rows are updated.