Rules for Adding or Dropping Constraints - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530

Batch Referential Integrity Constraints

Like standard referential integrity, batch referential integrity defines and enforces referential integrity constraints. However, batch and standard referential integrity validate constraints at different times and treat invalid rows differently. The rules for adding or dropping batch referential integrity constraints are identical to those for standard referential integrity constraints.

Referential Constraints

Referential Constraints provide the Optimizer with an efficient method for producing better query plans that take advantage of defined, but unenforced, referential relationships.

The candidate key for the referenced table in the constraint need not be declared to be unique.

The rules for Referential Constraints (also called soft referential integrity constraints) are identical to those documented in the following section. See Referential Constraints for additional information.

Standard Referential Integrity Constraints

Referential integrity is supported only on the Block File System on the primary cluster, not on the Object File System.

Referential integrity prevents rows in a child table from being orphaned from their referenced rows 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 following rules apply to adding and dropping referential integrity constraints.
  • 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 add or drop only one foreign key reference, which cannot contain 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 optional 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.
The following process describes the error handling processes for adding any new referential constraint to a table.
  1. 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 Rules and Restrictions for Error Tables).
    • 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[X] or RI_Distinct_ParentsV[X] system views.

    • 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.
  2. The error table is created under the same user or database as the table being altered.
    • If a table with the same name as that generated for the error table exists, the system returns an error.
    • If rows in the referencing table contain values in the foreign key columns that cannot be found in any row of the referenced table, the system 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 must correct values in the referenced or referencing tables so that full referential integrity exists between the two tables.

    You must maintain the error table, which you must delete after correcting any errors.