ADD referential-constraint

CONSTRAINT constraint-name
Names the constraint. A constraint-name must not identify a constraint that already exists at the current server.

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

FOREIGN KEY
Defines a referential constraint. FOREIGN KEY is not allowed if the table is a partitioned table.

Let T1 denote the table being altered.

(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 T1. The same column must not be identified more than once. The column must not be a LOB or DATALINK column. The number of the 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 allows nulls.
REFERENCES table-name
The table-name specified in a REFERENCES clause must identify a base table that exists at the current server, but it must not identify a catalog table, a global temporary table, a partitioned table, or a distributed table. This table is referred to as the parent table in the constraint relationship.

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 an existing referential constraint on the table. Duplicate referential constraints are allowed, but not recommended.

Let T2 denote the identified parent table.

(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 lengths must not exceed 32766-n, where n is the number of columns specified that allow nulls.

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 may be specified in any order. For example, if (A,B) is specified, a unique constraint defined as UNIQUE (B,A) would satisfy the requirement. 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 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 nth column of the parent key must have identical data types, lengths, and CCSIDs.

Unless the table is empty, the values of the foreign key must be validated before the table can be used. Values of the foreign key are validated during the execution of the ALTER TABLE statement. Therefore, every nonnull value of the foreign key must match some value of the parent key of T2.

The referential constraint specified by the 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. SET NULL and SET DEFAULT must not be specified if T1 has an update trigger.

CASCADE must not be specified if T1 has a delete trigger.

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.