16.10 - Semantic Constraint Specifications - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

A name and a data type must be specified for each column defined for a table. Each specified column can be further defined with one or more attribute (see SQL Data Types and Literals) or constraint definitions.

There are several different specifications for constraints, some of which apply to multiple categories of constraints.

You cannot declare semantic database constraints on columns defined with XML, BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, XML-based UDT, ARRAY/VARRAY, Period, JSON, or Geospatial data types.

The following constraints are SQL column definition attributes that specify column-level integrity constraints:

  • UNIQUE constraint definition on a single column.

    UNIQUE constraints are implemented as USIs.

    If a row-level security-protected table is defined with a UNIQUE constraint, enforcement of the UNIQUE constraint does not execute any row-level security policy defined for the table.

    UNIQUE constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.

    You can specify UNIQUE constraints on columns having a UDT data type as long as the UDT is not based on XML, BLOB, or CLOB data.

    You cannot specify UNIQUE constraints on columns having any of the following data types:

    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • ARRAY
    • VARRAY
    • Period
    • Geospatial
    • JSON

    You cannot define a UNIQUE constraint on a row-level security constraint column of a row-level security-protected table.

    If you do not specify either an explicit PRIMARY INDEX or NO PRIMARY INDEX, Teradata Database converts any UNIQUE constraints you define to either a unique primary index or a unique secondary index, depending on whether a primary key is also defined for the table (see Primary Index Defaults for details).

IF PrimaryIndexDefault is set to D or P and a CREATE TABLE request specifies this constraint on a column set without also specifying either a PRIMARY INDEX or NO PRIMARY INDEX option … THEN Teradata Database converts the …
PRIMARY INDEX column set defined as the primary key to the unique primary index for the table.

Any additional column sets defined with UNIQUE constraints are redefined as unique secondary indexes.

PRIMARY INDEX and UNIQUE constraints are implemented as unique secondary indexes. They are also explicitly redefined as unique secondary indexes in the SQL create text for the table definition.

UNIQUE first column set defined with a UNIQUE constraint to the unique primary index for the table.

Any other column sets defined with UNIQUE constraints are redefined as either unique secondary indexes.

  • CHECK constraint definition on a single column.

    CHECK constraints are not implemented as indexes.

    If a row-level security-protected table is defined with a CHECK constraint, enforcement of the constraint does not execute any security policy defined for the table.

    CHECK constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.

    You cannot define a CHECK constraint on a row-level security constraint column of a row-level security-protected table.

    You cannot specify CHECK constraints on columns having any of the following data types:

    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • ARRAY/VARRAY
    • UDT
    • Period
    • Geospatial
    • JSON
  • PRIMARY KEY constraint definition on a single column.

    PRIMARY KEY constraints are implemented as USIs.

    If a PRIMARY KEY constraint is defined where either or both the parent and child table are row-level security-protected, execution of the referential integrity constraint does not execute any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row-level security-protected.

    You can specify PRIMARY KEY constraints on columns having a UDT data type.

    You cannot specify PRIMARY KEY constraints on columns having any of the following data types:

    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON

    You cannot define a PRIMARY KEY constraint on a row-level security-protected column.

    If you do not specify an explicit PRIMARY INDEX or NO PRIMARY INDEX option, Teradata Database converts any PRIMARY KEY constraint you define for a table to a unique primary index (see Primary Index Defaults for complete details).

  • REFERENCES constraint definition on a single column.

    REFERENCES constraints are not implemented as indexes.

    If a REFERENCES constraint is defined where either or both the parent and child table are row-level security-protected, execution of the referential integrity constraint does not execute any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row-level security-protected.

    You cannot specify foreign key REFERENCES constraints on columns having any of the following data types.

    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON

      Temporal tables do not support foreign key REFERENCES constraints for standard or batch referential integrity.

See ANSI Temporal Table Support and Temporal Table Support for details.

The following constraints are SQL table definition attributes that specify table-level and intertable integrity constraints:

  • CHECK constraint definition on a composite column set.
  • FOREIGN KEY … REFERENCES constraint definition on a composite column set.
  • PRIMARY KEY constraint definition on a composite column set.
  • UNIQUE constraint definition on a composite column set.

You cannot specify constraints other than NULL or NOT NULL for global temporary trace tables. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language.

Performance Issues for Referential Integrity Constraints

The following set of topics describes some of the more important performance issues that are included in enforcing referential integrity.

For more information on referential integrity, see The Referential Integrity Rule and Foreign Key Constraints.

Benefits of Referential Integrity

Benefit        Description
Maintains data consistency Teradata Database enforces integrity relationships between tables based on the definition of a PK or a FK.
Maintains data integrity When performing INSERT, UPDATE, and DELETE requests, Teradata Database maintains data integrity between referencing and referenced tables.
Increases development productivity It is not necessary to code applications to enforce referential constraints because Teradata Database automatically enforces referential integrity.
Requires fewer programs to be written Teradata Database prevents update activities from violating referential constraints.

Teradata Database enforces referential integrity in all environments; you need no additional programs.

Overhead Costs of Referential Integrity

Overhead costs includes building the reference index subtables and inserting, updating, and deleting rows in the referencing and referenced tables. Overhead for inserting, updating, and deleting rows in the referencing table is similar to that of USI subtable row handling.

Teradata Database redistributes a row for each reference to the AMP containing the USI or reference index subtable entry. Processing differs after that, and most of the additional cost is in message handling.

When implementing tables with referential integrity, consider the following factors.

  • Most importantly, the performance impact to update operations, which is frequently slowed when a referential integrity constraint must be enforced.
  • INSERT performance slows for table because any referential integrity constraints defined for the table must be enforced.
  • The cost of extra disk space for table maintenance resulting from referential integrity constraints.
  • The cost of extra disk space for reference index subtables versus savings on program maintenance and increased data integrity.
  • The cost of DML integrity validity checking in applications versus the cost of not checking.

Join Elimination and Referential Integrity

Join elimination is a process undertaken by the Optimizer to eliminate redundant joins based on information from referential integrity constraints.

The following conditions eliminate a join.

  • A referential integrity relationship exists between the two tables.
  • Request conditions are conjunctive, meaning they are ANDed rather than ORed.

    This means that if any single condition in an ANDed set fails, the entire condition fails.

  • The request does not contain reference columns from the PK table, other than the PK columns, including the SELECT, WHERE, GROUP BY, HAVING, ORDER BY columns.
  • PK columns in the WHERE clause appear only in PK-FK joins.
IF… THEN…
the preceding conditions are met
  • the PK join is removed from the query.
  • all references to the PK columns in the query are mapped to the corresponding foreign key columns.
foreign key columns are nullable Teradata Database adds a NOT NULL condition to the request.

Standard Referential Integrity and Batch Referential Integrity

In standard referential integrity, whether you are doing row-at-time updates or set-processing INSERT … SELECT requests, each child row is separately matched to a row in the parent table, one row at a time. A separate SELECT request against the parent table is performed for each child row. Depending on your demographics, Teradata Database might select parent rows more than once.

With batch referential integrity, all of the rows within a single request, even if only one row is affected, are spooled, sorted, and their references checked in a single operation, as a join to the parent table. Depending on the number of rows in the INSERT … SELECT request, batch referential integrity could be considerably faster, compared to checking each parent-child relationship individually.

For row-at-time updates, there is very little difference between standard referential integrity and batch referential integrity. But if you plan to load primarily using INSERT … SELECT requests, batch referential integrity is recommended.

Referential Constraints

To maximize the usefulness of join elimination, you can specify Referential Constraints that Teradata Database does not enforce.

You can specify the REFERENCES WITH NO CHECK OPTION option to specify CREATE TABLE and ALTER TABLE statements with Referential Constraints, and the Optimizer can use the constraints without incurring the penalty of database-enforced referential integrity.

But when you use a REFERENCES WITH NO CHECK OPTION clause, Teradata Database does not enforce the Referential Constraints that you define. This means that a row having a non-null value for a referencing column can exist in a table even if an equal value does not exist in a referenced column. When you specify Referential Constraints, Teradata Database does not return error messages that would otherwise occur when RI constraints are violated.

If you specify a column name for a Referential Constraint, it need not refer to the single column PK of the referenced table or to a single column alternate key in the referenced table defined as UNIQUE, though best practice dictates that it should. The key acting as the PK in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.

The candidate key must always be unique even if it is not explicitly declared to be so, otherwise the referential integrity can produce incorrect results, and corrupt your databases if you do not take care to ensure that data integrity is maintained.

Specifying Referential Constraints relies heavily on your knowledge of your data. If the data does not actually satisfy the Referential Constraints that you provide, then requests can easily produce incorrect results.

The Optimizer can use the Referential Constraints without incurring the penalty of database-enforced referential integrity, but with the likelihood that Teradata Database can return corrupted result data.

Naming Constraints

Naming constraints is a good practice to follow because it permits a programmer to debug an embedded SQL or stored procedure application by fetching the name of a violated constraint from the SQLSTATE area. If constraints are not named, debugging is more difficult because it can be very difficult to determine which constraints belong to which system-defined names.

Row-level security constraints must be named. CHECK, UNIQUE, FOREIGN KEY, and PRIMARY KEY constraint specifications should be named.

Constraint names must conform to the rules for Teradata Database object names and be unique among all other constraint, primary index, and secondary index names specified on the table.

The characters used to name a constraint can be any of the following:

  • Uppercase and lowercase alphabetic characters
  • Integers
  • Any of the following special characters.
    • -
    • #
    • $

Teradata Database does not assign system-generated names to unnamed constraints.

CHECK Constraints

CHECK constraints are the most general type of SQL constraint specifications. Depending on its position in the CREATE or ALTER TABLE SQL text, a CHECK constraint can apply either to an individual column or to multiple columns.

Teradata Database derives a table-level index partitioning CHECK constraint from the partitioning expression for a PPI table. The text for this derived constraint cannot exceed 16,000 characters; otherwise, the system aborts the request and returns an error to the requestor. See Partitioning CHECK Constraints and Partitioning CHECK Constraints for Single-Level Partitioning for more information about this.

The following rules apply to all CHECK constraints.

  • You can define CHECK constraints at column-level or at table-level.
  • The specified predicate for a CHECK constraint must be a simple boolean search condition.

    Subqueries, aggregate expressions, and CASE expressions are not valid search conditions for CHECK constraint definitions.

  • You cannot specify CHECK constraints at any level for volatile tables or global temporary trace tables.
  • Be aware that a combination of table-level, column-level, and WITH CHECK OPTION on view constraints can create a constraint expression that is too large to be parsed for INSERT and UPDATE requests.
  • Teradata Database tests CHECK constraints for character columns using the current session collation.

    As a result, a CHECK constraint might be met for one session collation, but violated for another, even though the identical data is inserted or updated.

    The following is an example of the potential importance of this. A CHECK constraint is checked on insert and update of a base table character column, and might affect whether a sparse join index defined with that character column gets updated or not for different session character collations, in which case different request results might occur if the index is used in a query plan compared to the case where there is no sparse join index to use.

  • Teradata Database considers unnamed CHECK constraints specified with identical text and case to be duplicates, and returns an error when you submit them as part of a CREATE TABLE or ALTER TABLE request.

    For example, the following CREATE TABLE request is valid because the case of f1 and the case of F1 are different.

       CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (F1>0));

    The following CREATE TABLE request, however, is not valid because the case of the two unnamed f1 constraints is identical. This request aborts and returns an error to the requestor.

       CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (f1>0));
  • The principal difference between defining a CHECK constraint at column-level or at table-level is that column-level constraints cannot reference other columns in their table, while table-level constraints, by definition, must reference other columns in their table.
  • Columns defined with a data type from the following list cannot be a component of a CHECK constraint.
    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON
  • You cannot define a CHECK constraint on a row-level security constraint column of a row-level security-protected table.
  • If a row-level security-protected table is defined with one or more CHECK constraints, the enforcement of those constraints does not execute any UDF security policies that are defined for the table. The enforcement of the CHECK constraint applies to the entire table. This means that CHECK constraints apply to all rows in a row-level security-protected table, not just to the rows that are user-visible.

The following rules apply only to column-level CHECK constraints.

  • You can specify multiple column-level CHECK constraints on a single column.

    If you define more than one unnamed distinct CHECK constraint for a column, Teradata Database combines them into a single column-level constraint.

    However, Teradata Database handles each named column-level CHECK constraint separately, as if it were a table-level named CHECK constraint.

  • A column-level CHECK constraint cannot reference any other columns in its table.

The following rules apply only to table-level CHECK constraints.

  • A table-level constraint usually references at least two columns from its table.
  • Table-level CHECK constraint predicates cannot reference columns from other tables.
  • You can define a maximum of 100 table-level constraints for a table at one time.

Foreign Key Constraints

Foreign key constraints permit you to specify referential primary key-foreign key relationships between a unique column set in the current base table and an alternate key column set in a different base table. The FOREIGN KEY keywords are required for table-level foreign key definitions but cannot be used for column-level foreign key definitions;. If you specify FOREIGN KEY, then you must also specify a REFERENCES clause. Teradata Database uses referential integrity constraints to enforce referential integrity (see The Referential Integrity Rule and Inclusion Dependencies) and to optimize SQL requests (see SQL Request and Transaction Processing). The overhead of enforcing standard referential integrity constraints is summarized in Referential Integrity Constraint Checks and Overhead Costs of Standard Referential Integrity.

You can also use a foreign key definition to specify any of the following referential constraint types.

  • Standard Referential Integrity
  • Batch Referential Integrity
  • Referential Constraints
  • Temporal Relationship Constraints

Temporal tables can only be defined for so-called “soft” referential integrity relationships, or Referential Constraints, and for Temporal Relationship Constraints, and do not permit foreign key constraints for Standard or Batch Referential Integrity. See ANSI Temporal Table Support and Temporal Table Support or for details.

When you specify the REFERENCES WITH NO CHECK OPTION phrase, Teradata Database does not enforce the defined referential integrity constraint. This implies the following things about child table rows:

  • A row having a value, meaning that the implication is false if the referencing column is null, for a referencing column can exist in a table even when no equivalent parent table value exists.
  • A row can, in some circumstances, match multiple rows in its parent table when the referenced and referencing column values are compared.

    This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.

The various types have different applications as described in the following table:

Referential Constraint Type Application
Referential Integrity Constraint

(see “CREATE TABLE” in SQL Data Definition Language)

  • Tests each individual inserted, deleted, or updated row for referential integrity.
  • If insertion, deletion, or update of a row would violate referential integrity, then AMP software rejects the operation and returns an error message.
  • Permits special optimization of certain queries.
Batch Referential Integrity Constraint

(see “CREATE TABLE” in SQL Data Definition Language)

  • Tests an entire insert, delete, or update batch operation for referential integrity.
  • If insertion, deletion, or update of any row in the batch violates referential integrity, then parsing engine software rolls back the entire batch and returns an abort message.
  • Permits special optimization of certain queries.
Referential Constraint

(see “CREATE TABLE” in SQL Data Definition Language)

  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
Temporal Relationship Constraint (TRC)

(see ANSI Temporal Table Support and Temporal Table Support)

  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
  • TRC relationships can only be defined at the table level.
  • TRC relationships cannot be defined on self-referencing tables.

Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

Important: If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key-foreign key rows do not match.

For more information see Inclusion Dependencies and “CREATE TABLE” in SQL Data Definition Language.

The table on the following pages summarizes the differences among the different referential constraints.

Referential Constraint Type CREATE/ALTER TABLE Definition Clause Description Does It Enforce Referential Integrity? Level of Referential Integrity Enforcement Join EliminationOptimizations? Derived Statistics Propagated Between Child and Parent Tables? Pros Cons
Standard Referential Integrity Constraint REFERENCES ANSI/ISO SQL:2011 compliant.

Integrity enforcement done using a Referential Index (see Sizing a Reference Index Subtable).

Yes Row Yes Yes
  • ANSI/ISO compliant.
  • Logs RI violations in an error table.
  • Efficient for low volume updates.
Not efficient for medium to large updates because it is enforced one row at a time.
Batch Referential Integrity Constraint REFERENCES WITH CHECK OPTION Teradata extension to ANSI/ISO SQL:2011 standard.

All or nothing for update operations.

Enforced using the following methods:

  • Joining new child table keys to parent table to ensure they exist.
  • Joining deleted parent table keys to child table to ensure they do not exist.
Yes Implicit transaction Yes Yes Efficient for medium to large updates because uses the Optimizer to determine best way to make the join.
  • Can be slower than regular RI for small updates.
  • Does not log RI violations in an error table.
Referential Constraint

(“soft referential constraint”)

REFERENCES WITH NO CHECK OPTION Teradata extension to ANSI/ISO SQL:2011 standard.

No RI enforcement by Teradata Database. You must ensure the integrity of the relationship yourself.

Not necessary to define a UNIQUE constraint for the primary or alternate key column set.

No None Yes Yes No cost for enforcing RI or uniqueness.
  • Incorrect results or data corruption can occur if the RI constraint is not valid.
  • Not an issue if you are certain about the integrity of the relationship.
Temporal Relationship Constraint (“TRC constraint”) For more information on temporal relationship constraints, see ANSI Temporal Table Support , B035-1186 and Temporal Table Support , B035-1182 .

When you specify the REFERENCES WITH NO CHECK OPTION phrase, Teradata Database does not enforce the defined referential integrity constraint. This implies the following things about child table rows.

  • A row having a non-null value for a referencing column can exist in a table even when no equivalent parent table value exists.
  • A row can, in some circumstances, match multiple rows in its parent table when the referenced and referencing column values are compared.

    This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.

Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key-foreign key rows do not match.

For more information see Inclusion Dependencies and “CREATE TABLE” in SQL Data Definition Language.

Rules for Both Column-Level and Table-Level Foreign Key Constraints

The following rules apply to both column- and table-level FOREIGN KEY … REFERENCES constraints:

  • Teradata Database does not support the following ANSI/ISO SQL:2011 referential action options for FOREIGN KEY … REFERENCES constraints:
    • MATCH {FULL | PARTIAL | SIMPLE}
    • ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
    • ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
  • The specified column_name list must be identical to a set of columns in the referenced table that is defined as one of the following.
    • PRIMARY KEY
    • UNIQUE
    • A unique secondary index

      This rule is not mandatory for Referential Constraints. See Foreign Key Constraints for details.

      The specified table_name refers to the referenced table, which must be a user base data table, not a view.

  • A maximum of 64 foreign keys can be defined for a table and a maximum of 64 referential constraints can be defined for a table.

    Similarly, a maximum of 64 other tables can reference a single table. Therefore, there is a maximum of 128 Reference Indexes that can be stored in the table header per table.

    The limit on Reference Indexes includes both references to and from the table and is derived from 64 references to other tables plus 64 references from other tables = 128 Reference Indexes.

    However, only 64 Reference Indexes are stored per Reference Index subtable for a table, those that define the relationship between the table as a parent and its children.

    Column-level CHECK constraints that reference alternate keys in other tables do not count against this limit.

  • Each individual foreign key can be defined on a maximum of 64 columns.
  • Foreign key constraints cannot be defined on columns defined with any of the following data types:
    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON
  • Foreign key constraints cannot be defined on a global temporary trace table.
  • Note the following attributes of foreign key constraints:
    • They can be null.
    • They are rarely unique.

      An example of when a foreign key would be unique is the case of a vertical partitioning of a logical table into multiple tables.

  • Each column in the foreign key constraint must correspond with a column of the referenced table, and the same column name must not be specified more than once.
  • The referencing column list should contain the same number of column names as the referenced column list. The i th column of the referencing list corresponds to the i th column identified in the referenced list. The data type of each referencing column must be the same as the data type of the corresponding referenced column.
  • The user issuing the CREATE TABLE request that specifies a foreign key constraint must either have the REFERENCE privilege on the referenced table or on all specified columns of the referenced table.
  • If REFERENCES is specified in a column_constraint,then table_name defines the referenced table. Note that table_name must be a base table, not a view.
  • Referential constraints are not supported for global temporary, global temporary trace, or volatile tables.
  • While it is possible to create a child table at a time that its parent table does not yet exist, a REFERENCES constraint that makes a forward reference to a table that has not yet been created cannot qualify the parent table name with a database name.

    In other words, the forward-referenced parent table that has not yet been created must be assumed to be “contained” in the same database as its child table that is currently being created.

  • You cannot define a foreign key constraint on a row-level security-protected column.

Rules for Column-Level Foreign Key Constraints Only

The following rules apply to column-level foreign key constraints only.

  • You cannot specify the keywords FOREIGN KEY or specify a referencing column set in the definition of a column-level foreign key constraint.

    The referencing column for a column-level foreign key constraint is the column on which the foreign key constraint is defined by default.

  • You cannot define a multicolumn foreign key constraint using the column-level foreign key syntax.
  • If you omit column_name, the referenced table must have a single-column primary key, and the specified foreign key column references that primary key column of the referenced table.
  • If you specify column_name, it must refer to the single-column primary key of the referenced table or to a single-column alternate key in the referenced table defined as UNIQUE.

    This rule does not apply for Referential Constraints. In such cases, the candidate key acting as the primary key in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.

    However, the candidate key in the relationship actually must always be unique even if it is not explicitly declared to be so. This is true by definition. See Identifying Candidate Primary Keys and PRIMARY KEY Constraints for details.

    The uniqueness rule for candidate keys is necessary because the Optimizer always assumes that candidate keys are unique when it generates its query plans, and if that assumption is not true, it is not only possible to produce incorrect results to queries, but to corrupt databases. Because of this, you must always ensure that all candidate key values are unique even if they are not explicitly declared to be so.

    As is always true when you specify Referential Constraints, you must assume responsibility for ensuring that any candidate key in a referential integrity relationship is unique, just as you must assume responsibility for ensuring that the referential relationship it anchors holds true in all cases, because Teradata Database enforces neither constraint.

Rules for Table-Level FOREIGN KEY … REFERENCES Constraints Only

The following rules applies to table-level FOREIGN KEY … REFERENCES constraints only.

  • You must specify a complete FOREIGN KEY (referencing_column_set) REFERENCES (referenced_table_name) specification for the foreign key definition.
  • If you do not specify the optional referenced_column_set in the foreign key definition, Teradata Database assumes that the columns in the referencing column set have the identical names as the implied columns in the referenced column set.
  • If the referenced column set columns do not have the same names as their counterparts in the referencing_column_set list, you must specify their names using the FOREIGN KEY (referencing_column_set) REFERENCES referenced_table_name (referenced_column_set) syntax.
  • The optional keywords WITH CHECK OPTION and WITH NO CHECK OPTION define a foreign key constraint as being either a batch referential integrity constraint or a Referential Constraint, respectively.

    If you specify neither set of keywords, the foreign key constraint defines a traditional foreign key constraint by default.

  • A maximum of 100 table-level constraints can be defined for any table.

Rules for FOREIGN KEY … REFERENCES Referential Constraints Only

Other than their not actually enforcing referential integrity, most of the rules for Referential Constraints are identical to those documented by Rules for Both Column-Level and Table-Level Foreign Key Constraints and by Rules for Table-Level FOREIGN KEY ... REFERENCES Constraints Only.

The exceptions are documented by the following set of rules that apply specifically to the specification and use of Referential Constraints.

  • You can specify standard RI, batch RI, and Referential Constraints in the same table, but not for the same column set.
  • You can specify Referential Constraints for both of the following constraint types:
    • FOREIGN KEY (FK_column_set) REFERENCES (parent_table_PK_column_set)
    • (NFK_column_set) REFERENCES (parent_table_AK_column_set)

      where NFK indicates non-foreign key and parent_table_AK_column_set indicates an alternate key in the parent table.

  • Referential Constraint references count toward the maximum of 64 references permitted for a table referenced as a parent even though they are not enforced by the system.
  • INSERT, DELETE, and UPDATE requests are not permitted against tables with unresolved, inconsistent, or non-valid Referential Constraints. This rule is identical to the rule enforced for standard and batch RI.
  • The candidate key acting as the primary key in the referenced table in the constraint need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.

Referential Integrity Constraint Checks

Teradata Database performs referential integrity constraint checks whenever any of the following things occur:

  • A referential integrity constraint is added to a populated table.
  • A row is inserted or deleted.
  • A parent or foreign key is modified.

The following table summarizes these actions:

Action Taken Constraint Check Performed
INSERT into parent table None.
INSERT into child table Must have matching parent key value if the foreign key is not null.
DELETE from parent table Abort the request if the deleted parent key is referenced by any foreign key.
DELETE from child table None.
UPDATE parent table Abort the request if the parent key is referenced by any foreign key.
UPDATE child table New value must match the parent key when the foreign key is updated.

Overhead Costs of Standard Referential Integrity

By implementing standard referential integrity, you incur certain overhead costs that can have a negative effect on performance. The following table lists the various referential integrity overhead operations that affect performance.

Operation Description
Insert, update, or delete rows in a referencing table. Overhead is similar to that for USI subtable row handling.

A redistributed spool for each reference is dispatched to the AMP containing the subtable entry.

BYNET traffic incurs the majority of the cost of this operation.

Insert a row into a referencing table A referential integrity check is made against the Reference Index subtable.
  • If the referenced column is in the Reference Index subtable, the count in the Reference Index subtable is incremented.
  • If the referenced column is not in the Reference Index subtable, Teradata Database first checks the Reference Index subtable to verify that the referenced column exists.

    If it does, an entry with a count of 1 is added to the Reference Index subtable.

Delete a row from a referencing table A referential integrity check is made against the Reference Index subtable and its count for the referenced field is decremented.

When the count decrements to 0, then the subtable entry for the Referenced field is deleted.

Update a referenced field in a referencing table Overhead is similar to that for changing the value of a USI column.

A referential integrity check is made against the Reference Index subtable. Both the inserting-a-row and deleting-a-row operations execute on the Reference Index subtable, decrementing the count of the old Referenced column value and incrementing the count of the new Reference column value.

Delete a row from a referenced table The Reference Index subtable is checked to verify that the corresponding Referenced column does not exist. When nonexistence is confirmed, the row is deleted from the Referenced table.

No BYNET traffic is involved because the Referenced column is the same value in the Referenced table and the Reference Index subtable.

PRIMARY KEY Constraints

PRIMARY KEY constraints specify the primary key column set in a table definition. Teradata Database uses primary keys to enforce both row uniqueness and referential integrity.

Whether a PRIMARY KEY constraint is treated as a column-level constraint or a table-level constraint depends on whether the primary key is simple or composite.

The following rules apply to PRIMARY KEY constraints.

  • Only one primary key can be defined per table.
  • The following table explains the column limits for column-level and table-level primary key constraints.
IF the PRIMARY KEY constraint is … THEN you must define it at this level …
simple, or defined on a single column column.

You can define a simple PRIMARY KEY constraint at table-level, but there is no reason to do so.

composite, or defined on multiple columns table.

Defining a table-level PRIMARY KEY constraint is the only way you can create a multicolumn primary key.

  • Defining a primary key for a table is never required, though it is recommended for documentation purposes as part of a policy of enforcing data integrity in those cases where the logical primary key is not chosen to be the unique primary index.
  • A primary key can be defined on a maximum of 64 columns.
  • A PRIMARY KEY constraint cannot be defined on the same column set as the set used to define the nonunique primary index for a table.
PRIMARY KEY constraints are treated as … When the primary key is defined on this many columns …
column-level constraints 1
table-level constraints > 1

A maximum of 100 table-level constraints can be defined for any table.

  • A primary key constraint can be defined on the same columns as a unique secondary index or unique primary index. A primary key is implemented as follows:
    • If the table is defined explicitly with a primary index or unique primary index on different columns than the primary key columns or if the table is defined as NO PRIMARY INDEX or PRIMARY AMP INDEX, then the primary key is implemented as a unique secondary index.
    • If a nonpartitioned, nontemporal table does not have an explicit primary index, explicit primary AMP index, or explicit NoPI and no USI or UNIQUE constraint is specified on the same columns as the primary key, then the default is a unique primary index on the same columns as the primary key constraint.

    Note that in physical database design, candidate keys, whether chosen to be a primary key or not, are always defined internally as either a UNIQUE NOT NULL secondary index or as a single-table join index.

  • PRIMARY KEY constraints cannot be defined on columns defined with any of the following data types.
    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON
  • PRIMARY KEY constraints cannot be defined on a global temporary trace table.
  • You cannot define a PRIMARY KEY on a row-level security-protected column.

UNIQUE Constraints

UNIQUE constraints specify that the column set they modify must contain unique values. Teradata Database implements UNIQUE constraints as either a unique primary index, a unique secondary index, or as a single-table join index.

The following rules apply to UNIQUE constraints:

  • UNIQUE constraints should always be specified with a NOT NULL attribute specification.

    Otherwise, it is possible for a single null to be inserted into a uniquely constrained column. The semantics of a unique null “value” are uncertain at best, and almost certainly violate the intent of the uniqueness constraint.

  • UNIQUE constraints can be defined at column-level (simple) or at table-level (composite).

    The following table explains the column limits for column-level and table-level primary key constraints.

IF the UNIQUE constraint is … THEN you must define it at this level …
simple, or defined on a single column column.

You can define a simple UNIQUE constraint at table-level, but there is no reason to do so.

composite, or defined on multiple columns table.

Defining a table-level constraint is the only way you can create a multicolumn UNIQUE constraint.

  • Column-level UNIQUE constraints refer only to the column on which they are specified.
  • Table-level UNIQUE constraints can be defined on multiple columns by specifying a column name list.
  • A table-level UNIQUE constraint can be defined on a maximum of 64 columns.
  • A maximum of 100 table-level constraints can be defined for any table.
  • You can define a UNIQUE constraint for a column-partitioned table.
  • UNIQUE constraints cannot be defined on columns defined with any of the following data types:
    • XML
    • BLOB
    • CLOB
    • XML-based UDT
    • BLOB-based UDT
    • CLOB-based UDT
    • ARRAY/VARRAY
    • Period
    • Geospatial
    • JSON
  • UNIQUE constraints cannot be defined on a global temporary trace table.
  • If a row-level security-protected table is defined with a UNIQUE constraint, enforcement of the constraint does not execute any security policy defined for the table. UNIQUE constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.
  • You cannot define a UNIQUE constraint on a row-level security constraint column of a row-level security-protected table.