REFERENCES table-name ornickname
The tableor nickname specified in a REFERENCES clause must identify a base tableor anickname that is described in the catalog, but must not identify a catalog table.
A referential constraint is a duplicate if its foreign key, parent key, and parent tableor parent nickname are the same as the foreign key, parent key, and parent tableor parent nickname of a previously specified referential constraint. Duplicate referential constraints are ignored, and a warning is returned (SQLSTATE 01543).
In the following discussion, let T2 denote the identified parent table, and let T1 denote the table being created (or altered). (T1 and T2 may be the same table).
The specified foreign key must have the same number of columns as the parent key of T2 and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule.
(column-name,...)
The parent key of a 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 list of column names must match the set of columns (in any order) of the primary key or a unique constraint that exists on T2 (SQLSTATE 42890). If a column name list is not specified, then T2 must have a primary key (SQLSTATE 42888). Omission of the column name list is an implicit specification of the columns of that primary key in the sequence originally specified.
The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
rule-clause
Specifies what action to take on dependent tables.
ON DELETE
Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are four possible actions:
* NO ACTION (default)
* RESTRICT
* CASCADE
* SET NULL
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.
SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.
If T1 is delete-connected to T2 through multiple paths,defining two SET NULL rules with overlapping foreign key definitionsis not allowed.For example: T1 (i1, i2, i3).Rule with foreign key (i1, i2) and Rule2 with foreign key (i2, i3)is not allowed.
The firing order of the rules is:
1.RESTRICT
2.SET NULL OR CASCADE
3.NO ACTION
If any row in T1 is affected by two different rules, anerror occurs and no rows are deleted.
A referential constraint cannot be defined if it wouldcause a table to be delete-connected to itself by a cycle involvingtwo or more tables, and where one of the delete rules is RESTRICT orSET NULL (SQLSTATE 42915).
A referential constraint that would cause a table to bedelete-connected to either itself or another table by multiple pathscan be defined, except in the following cases (SQLSTATE 42915):
* A table must not be both a dependent table in a CASCADErelationship (self-referencing, or referencing another table), andhave a self-referencing relationship in which the delete rule isRESTRICT or SET NULL.
*A key overlaps another key when at least one column in one keyis the same as a column in the other key.When a table is delete-connected to another table through multiplerelationships with overlapping foreign keys, those relationshipsmust have the same delete rule, and none of the delete rules can beSET NULL.
*When a table is delete-connected to another table throughmultiple relationships, and at least one of those relationships isspecified with a delete rule of SET NULL, the foreign keydefinitions of these relationships must not contain any partitioningkey or MDC key column.
*When two tables are delete-connected to the same table throughCASCADE relationships, the two tables must not be delete-connectedto each other if the delete rule of the last relationship in eachdelete-connected path is RESTRICT or SET NULL.
If any row in T1 is affected by different delete rules,the result would be the effect of all the actions specified by theserules.AFTER triggers and CHECK constraints on T1 will also see the effectof all the actions.An example of this is a row that is targeted to be set null throughone delete-connected path to an ancestor table, and targeted to bedeleted by a second delete-connected path to the same ancestortable.The result would be the deletion of the row.AFTER DELETE triggers on this descendant table would be activated,but AFTER UPDATE triggers would not.
In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.
ON UPDATE
Specifies what action is to take place on the dependent tables when a row of the parent table is updated. The clause is optional. ON UPDATE NO ACTION is the default and ON UPDATE RESTRICT is the only alternative.
|
