Referential integrity is a rule that prevents rows in a child table from being orphaned from the rows they refer to in a parent table. When a referential integrity relationship is established between a primary or alternate key for a parent table and a foreign key in a child table, the system does not permit you to update or delete the referenced parent table key without first updating or deleting rows containing the foreign key in the child table.
- The user issuing the ALTER TABLE request containing a REFERENCES option must have the REFERENCES privilege on the referenced table or on all specified columns of the referenced table.
- You can only add or drop one foreign key reference and it cannot contain any options other than to add or drop the foreign key.
- The columns in the referenced parent table must be defined uniquely, either as the primary key for the table, with a UNIQUE column attribute, or as a USI.
This rule is not mandatory for Referential Constraints, where the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See Referential Constraints.
- The foreign key columns in the referencing child table must be identical to the key in the referenced table.
- You cannot define a referential constraint on a LOB column in a child table, nor can you define a referential constraint to a LOB column in the parent table.
- You cannot add either of the following constraints to a queue table definition.
- FOREIGN KEY … REFERENCES
- REFERENCES
- You cannot add either of the following constraints to a non-queue table definition if the target table for that reference is a queue table.
- FOREIGN KEY … REFERENCES
- REFERENCES
- You cannot compress either key value in a referential relationship. The system does not compare column-level constraints for the referentially related columns.
- Referential integrity is not supported for global temporary, trace, queue, or volatile tables.
See Teradata Vantage™ - Database Design, B035-1094 for more information about general aspects of referential integrity and its maintenance.
The following process describes the error handling processes involved in adding any new referential constraint, whether it checks referential integrity or not, to a table.
- The system generates an error table when the new constraint is added to the table. This is a different error table than the one you create to log minibatch bulk loading errors (see CREATE ERROR TABLE).
- Its name is the name of the target table suffixed with the appropriate reference index number.
A different reference index number is assigned to each foreign key constraint defined for the table. You can determine reference index numbers using the RI_Distinct_ChildrenV or RI_Distinct_ParentsV system views (see Teradata Vantage™ - Data Dictionary, B035-1092).
- Its columns and primary index are the same as those of the target table. If the primary index is partitioned, then the partitioning columns in the parent and child tables must also be the same.
- Its name is the name of the target table suffixed with the appropriate reference index number.
- The error table is created under the same user or database as the table being altered.
IF … THEN the system … a table with the same name as that generated for the error table already exists returns an error. rows in the referencing table contain values in the foreign key columns that cannot be found in any row of the referenced table does not return an error. Instead, a row is inserted into the error table for each such row found in the target table.
Use these rows to determine the corrections that must be made.
You are responsible for correcting values in the referenced or referencing tables so that full referential integrity exists between the two tables.
You are also responsible for maintaining the error table, including deleting it after any errors have been corrected.