Skip to Content

CREATE FOREIGN KEY statement

The CREATE FOREIGN KEY statement defines a foreign key for a table’s referential integrity checking.

GUID-FB797144-EE95-416F-8C98-7A415B75DAE3-low.png

CREATE FOREIGN KEY tableOwner 1.tableName 1

Specifies the name of the constraint for the foreign key. CREATE FOREIGN KEY tableOwner1.tableName1.constraintName specifies the fully qualified name. If constraintName is not specified, Db2 automatically generates a name.

REFERENCETB tableOwner 2. tableName 2

Specifies the name of the table to which the foreign key references.

KEYCOLUMNS (columnName, ...)

Specifies the columns that are included in the definition of the foreign key.

REFCOLUMNS (columnName, ...)

Specifies the names of the parent key columns.

Warning

Important

REFCOLUMNS should only be specified when the foreign key constraint name is not specified. To change the parent column list, alter the unique index that the foreign key uses.

ON DELETE

Specifies the new rule that determines the action to take when a row of the parent table for the foreign key is deleted.

ON DELETE parameter values

Value

Description

CASCADE

Specifies a delete rule of CASCADE.

RESTRICT

Specifies a delete rule of RESTRICT if the value of the CURRENT RULES special register is Db2.

SET NULL

Specifies a delete rule of SET NULL if a column of the foreign key allows null values.

NO ACTION

Specifies a delete rule of NO ACTION if the value of the CURRENT RULES special register is SQL.

ENFORCED

Specifies whether Db2 enforces the referential constraint.

ENFORCED parameter values

Value

Description

YES

Specifies that Db2 enforces the referential constraint.

NO

Specifies that Db2 does not enforce the referential constraint.

Related topic

CDL-statements

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

ALTER and BMC AMI Change Manager for Db2 13.1