Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see ALTER and BMC AMI Change Manager for Db2 13.1.

ALTER FOREIGN KEY statement

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


GUID-13F09709-14BB-427E-91ED-BA811EE6EC91-low.png

ALTER FOREIGN KEY tableOwner 1 .tableName 1 .constraintName 1

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

NAME constraintName 2

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 2. tableName 2

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.

Note

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.

Note

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.


Related topic

CDL-statements


 

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