Examples: Constraint Attribute - Teradata Vantage

Specifying Table-Level Named CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY Constraints

The request in this example names constraints at the table level.

CREATE TABLE good_2 (
  column_1 INTEGER NOT NULL,
  column_2 INTEGER NOT NULL,
  column_3 INTEGER NOT NULL,
  column_4 INTEGER NOT NULL,
  column_5 INTEGER,
  column_6 INTEGER, 
  CONSTRAINT primary_1
  PRIMARY KEY (column_1, column_2),
  CONSTRAINT unique_1
  UNIQUE (column_3, column_4),
  CONSTRAINT check_1
  CHECK (column_3 > 0 OR column_4 IS NOT NULL),
  CONSTRAINT reference_1
  FOREIGN KEY (column_5, column_6) 
  REFERENCES parent_1 (column_2, column_3));

Specifying Mix of Column- and Table-Level Named and Unnamed PRIMARY KEY, UNIQUE, and FOREIGN KEY Constraints

This statement defines named and unnamed constraints at the column and table levels.

CREATE TABLE good_3 (
  column_1 INTEGER NOT NULL
  CONSTRAINT primary_1 
  PRIMARY KEY,
  column_2 INTEGER NOT NULL
  CONSTRAINT unique_1 UNIQUE
  CONSTRAINT check_1
   CHECK (column_2 <> 3)
  CONSTRAINT reference_1
   REFERENCES parent_1
   CHECK (column_2 > 0)
   REFERENCES parent_1 (column_4),
  column_3 INTEGER NOT NULL,
  column_4 INTEGER NOT NULL,
  column_5 INTEGER,
   CONSTRAINT unique_2 UNIQUE (column_3),
   CONSTRAINT check_2
   CHECK (column_3 > 0 AND column_3 < 100),
   CONSTRAINT reference_2
 FOREIGN KEY (column_3)
 REFERENCES parent_1 (column_5), UNIQUE (column_4),
   CHECK (column_4 > column_5),
 FOREIGN KEY (column_4, column_5)
 REFERENCES parent_1 (column_6, column_7));

Example: Specifying a Mix of Column-Level and Table-Level Named and Unnamed CHECK Constraints

The request in this example combines the three unnamed CHECKs for column_1. Constraint check_0 and each of the named CHECKs for column_2 are treated as table constraints.

    CREATE TABLE good_4 (
      column_1 INTEGER
        CHECK (column_1 > 0)
        CHECK (column_1 < 999)
        CHECK (column_1 NOT IN (100,200,300))
       CONSTRAINT check_0
        CHECK (column_1 IS NOT NULL),
      column_2 INTEGER
       CONSTRAINT check_1
        CHECK (column_2 > 0)
        CHECK (column_2 < 999));

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.

       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);

Example: Specifying Column-Level Named CHECK Constraints

The request in this example names constraints at the column level.

    CREATE TABLE good_1 (
      column_1 INTEGER NOT NULL
       CONSTRAINT primary_1 PRIMARY KEY,
      column_2 INTEGER NOT NULL
       CONSTRAINT unique_1 UNIQUE,
      column_3 INTEGER
       CONSTRAINT check_1 CHECK (column_3 > 0);