ALTER FOREIGN KEY statement

The ALTER FOREIGN KEY statement defines changes to a foreign key.

ALTER FOREIGN KEY tableOwner ₁ .tableName ₁ .constraintName ₁

This option specifies the fully qualified name of the foreign key to be changed.

NAME constraintName ₂

This option specifies the new name of the foreign key. If the NAME parameter is not specified, Db2 automatically generates the name of the foreign key.

REFERENCETB tableOwner ₂. tableName ₂

This option specifies the name of the table to which the foreign key references.

KEYCOLUMNS (columnName, ...)

This option specifies the columns that are included in the definition of the foreign key.

REFCOLUMNS (columnName, ...)

This option specifies the names of the parent key columns.

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

This option specifies the new rule that determines the action to take when a row of the parent table for the foreign key is deleted. The following table lists valid values for the ON DELETE parameter.

Important

You should not build ALTER FOREIGN KEY statement when a foreign key changes

From referencing a primary key to referencing a unique key
To a new unique key
From a unique key reference to a primary key reference

In these instances, you should build a DROP FOREIGN KEY statement and a CREATE FOREIGN KEY instead of an ALTER FOREIGN KEY statement.

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

This option 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.

ALTER FOREIGN KEY statement

On this page