SQL Statements › CREATE TABLE › Referential Constraint Definition › Referential Actions That Conflict

Referential Actions That Conflict

A foreign key is rejected if the table involved has more than one foreign key and the referential actions of any two of those foreign keys conflict. One of two conditions are generally responsible for conflicts between referential actions:

1. A delete on a referenced table can propagate conflicting referential actions to a row of a referencing table. For example, if two foreign keys on table A reference table B, and one foreign key specifies ON DELETE CASCADE but the other specifies ON DELETE SET DEFAULT, then it becomes impossible to update table A in a way that satisfies both foreign keys.
2. The order in which foreign key references are followed and satisfied affects the resulting actions taken on referencing rows. It might initially seem that all situations described in the first condition (previously described) would also be included here. For example, if the SET DEFAULT described in the first condition (previously described) were executed before the CASCADE, the stored default values could effectively sever the row's other foreign key connection to the deleted row in table B, in which case the ON DELETE CASCADE would never be executed. But this second condition can occur even if the delete actions agree.

   Consider a case in which both foreign keys on table A specify ON DELETE SET NULL and the columns used in the two keys partially overlap. Setting any of the overlapping columns to NULL would sever both foreign keys' connections, because a NULL in any column of a foreign key severs the connection to the parent row. The first foreign key that is executed would therefore have its columns set to NULL, but the second foreign key would never be executed.

A table listing referential actions that conflict is provided in the following. When referring to the table, please note that it should be interpreted with regard to the following:

• An update action only conflicts with a delete action if the first condition on the previous page is violated, that is to say only if a DELETE on some indirect parent of a table can propagate both an ON UPDATE RESTRICT and a conflicting delete action to the table using two separate foreign key paths at the same time. A DELETE can propagate ON UPDATE RESTRICT because when an ON DELETE SET DEFAULT or ON DELETE SET NULL is executed on a table that is in turn referenced by another foreign key, the update actions on a referencing child table are executed instead of the delete actions.
• Referential actions that would normally conflict are allowed if the foreign keys involved have disjoint sets of parent tables (including both direct and indirect parents), or if the sets of columns affected by the conflicting rules are disjoint. SET NULL and SET DEFAULT actions that are not listed as being in conflict in the following table are, however, rejected if the second condition described on the previous page exists. But that second condition does not apply if the two foreign keys involved contain either totally disjoint or completely matching sets of columns on the child table.

In the following table, the abbreviations describe CONFLICTS and represent the following:

ODR

Represents ON DELETE RESTRICT.

ODC

Represents ON DELETE CASCADE.

ODSD

Represents ON DELETE SET DEFAULT.

ODSN

Represents ON DELETE SET NULL.

OUR

Represents ON UPDATE RESTRICT.

Note: The asterisk (*) means it does not conflict if defaults for all columns involved are NULL.

Note: Foreign keys can also be rejected for reasons not mentioned here. For a complete list of reasons, see the SQL return codes section in the CA Datacom/DB Message Reference Guide.