Allows you to specify a foreign key.
Foreign keys define relationships between tables. The column(s) of a foreign key in one table are related to the primary or unique key of some table. That related primary or unique key may not be defined on the same table as the foreign key.
Every foreign key is related to a primary or unique key. A primary or unique key may be related to zero, one, or many foreign keys.
Each foreign key defines a referential integrity constraint. Every foreign key value (the column values of the foreign key's columns from a single row of the table) must exactly match a primary or unique key value of the foreign key's related primary or unique key, except when at least one foreign key column value is NULL. When a foreign key value exists which does not match any primary or unique key value of its related primary or unique key, the referential integrity constraint is violated.
The foreign key constraint does not restrict the number of rows with the same foreign key value, that is to say, the constraint is "1-to-many," one referenced table row may match multiple referencing rows. You may define a "1-to-1" constraint by also defining the foreign key columns in a primary key or unique constraint.
An attempt to INSERT or UPDATE a foreign key value that does not exist in the referenced primary/unique key is rejected as a foreign key value error. An attempt to DELETE or UPDATE a primary/unique value that is referenced by one or more foreign key values is rejected as a foreign key reference error.
When the referenced table is loaded or recovered, the integrity of the foreign key is in doubt, and the table is placed in a CHECK-RELATED check state. A table in a check state cannot be opened. You must execute the CONFIRM function of DBUTLTY to confirm that all referenced values still exist. The check state is reported in the Directory (CXX) report.
Note: Tables defined using the SQL CREATE TABLE statement have the RECOVERY attribute-type of the TABLE entity-occurrence set to Y (for yes) in CA Datacom Datadictionary.
A "physical" key is not generated for a foreign key. However, if you define a key, it is used when checking the DELETE or UPDATE of the referenced primary/unique key. For best performance, the key should include all columns of the foreign key as the only or leading columns. Other columns may be included following the foreign key columns. The order in which foreign key columns are specified in the key does not affect performance.
A foreign key is rejected if the table involved has more than one foreign key and the delete or update actions of any two of those foreign keys conflict. A -36 SQL return code is issued if a conflict occurs. For more information see Referential Actions That Conflict.
Use the column-list to specify one or more column names. The column names must be separated by commas and the list must be enclosed in parentheses.
The number of columns in the column-list and their data type, length, and scale must be identical to those specified in the ref-col-list or the default ref-col-list.
When at least one of the columns is not defined with NOT NULL, that is to say, it may contain the null value, the reference is said to be "optional." When at least one column is the null value, no check is made for a matching primary/unique value.
Specify the name of the table you want to reference. The table-name must identify a table described in the CA Datacom Datadictionary other than a CA Datacom Datadictionary table. When no ref-col-list is specified, the table-name must have a PRIMARY KEY, and its column(s) is the default ref-col-list. The referenced table cannot be a remote table. Reference to a table in a different schema is permitted.
Use the column-list to specify one or more column names. The column names must be separated by commas and the list must be enclosed in parentheses. When you are specifying a ref-col-list, specify column(s) of a UNIQUE or PRIMARY KEY constraint on the table-name.
Specifies the referential action to be taken when a row in the referenced table is deleted or updated.
Following is the syntax diagram for ref-act (referential action):
►►─┬─ delete-action ───────────────┬──────────────────────────────────────────►◄ ├─ update-action ───────────────┤ ├─ delete-action update-action ─┤ └─ update-action delete-action ─┘
Expansion of Where delete-action is defined as
├── ON DELETE ─┬─ RESTRICT ◄ ──┬───────────────────────────────────────────────┤ ├─ CASCADE ─────┤ ├─ SET DEFAULT ─┤ └─ SET NULL ────┘
Expansion of Where update-action is defined as
├── ON UPDATE RESTRICT ────────────────────────────────────────────────────────┤
Specifies which delete-action is to be taken. Errors that occur during delete-actions cause the original DELETE (and all propagated delete-actions) to be aborted.
Delete-actions may be one of the following:
ON DELETE RESTRICT is the default delete-action. It specifies that there shall be no matching rows when a row is deleted from the referenced table. Matching rows are (for a given row in the referenced table) all rows in the referencing table whose referencing columns equal the corresponding referenced column. If referencing rows exist, the DELETE is rejected with a -175 return code.
Specifies that referencing rows are deleted after any references to the child table are followed and those referential actions performed.
Specifies that each column in the referencing foreign key is set to its default value for every referencing row. If ON DELETE SET DEFAULT is specified, all of the columns in the foreign key must have defaults defined.
Specifies that nullable columns in the referencing foreign key are set to NULL for all referencing rows. At least one column is guaranteed to be nullable in this foreign key. If ON DELETE SET NULL is specified, at least one of the foreign key columns must be nullable.
Specifies which update action is to be taken. RESTRICT is the only update action supported. RESTRICT is the default and is enforced even if not specified.
ON UPDATE RESTRICT specifies that there shall be no matching rows when a row is updated in the referenced table. Matching rows are (for a given row in the referenced table) all rows in the referencing table whose referencing columns equal the corresponding referenced column. If referencing rows exist, the UPDATE is rejected with a -175 return code.
|
Copyright © 2014 CA.
All rights reserved.
|
|