17.05 - REFERENCES Constraint Column Attribute - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
REFERENCES as a column attribute. The syntax varies depending on whether or not the constraint is named:
  • For an unnamed REFERENCES column constraint.
         REFERENCES referenced_table_name (referenced_column_name)
  • For a named REFERENCES column constraint.
         CONSTRAINT constraint_name REFERENCES referenced_table_name
                                   (referenced_column_name)
The following rules apply to column attribute REFERENCES constraints only.
  • Do not specify the SQL text FOREIGN KEY (referencing_column ) for a column attribute foreign key constraint. The referencing column is implicit in a column foreign key constraint and the FOREIGN KEY keywords are only used for table constraint foreign key specifications.
  • If you do not specify column_name , the referenced table must have a simple primary key, and the specified foreign key column references that primary key column in the referenced table.
  • If you specify column_name, it must refer to the simple primary key of the referenced table or to a simple alternate key in the referenced table that is 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 unique. Otherwise, you can produce incorrect results and corrupt your databases in some situations when appropriate care is not taken to ensure that data integrity is maintained. This is true by definition. For more information, see Teradata Vantage™ - Database Design, B035-1094.

    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 Vantage enforces neither constraint.

referenced_table_name
The name of the table that contains the primary key or alternate key referenced by the specified referencing_column.
(referenced_column_name)
The name of the column in referenced_table_name that contains the primary key or alternate key referenced by referencing_column.
CONSTRAINT constraint_name
The name of the column attribute foreign key constraint.

Example: Specifying Referential Integrity Constraints

In this example, table_1 is created with column-level and table-level foreign key constraints that specify the referential integrity constraints described in the following table.

This specific reference in the example … Is this level constraint …
explicit foreign key columns (column_1, column_2) in table_1 defining a foreign key to table_2.

For this to work, there must be columns identically typed to the (column_1,column_2) columns in table_1 (call them upi_column_1 and upi_column_2) that constitute the unique primary index columns for table_2.

In other words, columns upi_column_1 and upi_column_2 in table_2 must both be defined as type INTEGER and must constitute the unique primary index for table_2.

Otherwise, an error is returned.

Table
implicit foreign key column_1 in table_1 explicitly referencing column_1 in table_3.

table_1 (column_1) and table_3 (column_1) must both be typed INTEGER, but table_3 (column_1) need not be the unique primary index for table_3.

Column
       CREATE TABLE table_1
        (column_1 INTEGER NOT NULL REFERENCES table_3(column_1),
         column_2 INTEGER,
       FOREIGN KEY (column_1, column_2) REFERENCES table_2)
       PRIMARY INDEX (column_1);

Example: Specifying a Column-Level Foreign Key Constraint

The first request creates a column-level referential integrity constraint on column a1 with column b1 in table b. Referential integrity is enforced for this relationship.

The second request specifies a column-level Referential Constraint relationship on column a1 with column b1 in table b. Referential integrity is not enforced for this relationship.

    CREATE TABLE a (
     a1 INTEGER REFERENCES b(b1),
     a2 CHARACTER(10))
    PRIMARY INDEX (a1); 
    
    CREATE TABLE a (
     a1 INTEGER REFERENCES WITH NO CHECK OPTION b(b1),
     a2 CHARACTER(10))
    PRIMARY INDEX (a1);