Use of Constraint Types and Keys With CREATE TABLE | Teradata Vantage - Integrity Constraint Levels - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

You can specify integrity constraints as either column attributes or as table attributes.

A constraint that applies only to an element of a single column in a base table is referred to as a column attribute constraint.

Consider the following table definition.

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

Table good_1 defines the following three column attribute constraints.

  • A simple PRIMARY KEY constraint named primary_1 on good_1.column_1.

    For this constraint, the values inserted into good_1.column_1 must uniquely identify the row. If the constraint is violated, the database rejects the insertion of the row.

    Because PRIMARY KEY values should not be updated, this constraint is generally not violated for updates, but if is, the database rejects the update.

  • A simple UNIQUE constraint named unique_1 on good_1.column_2.

    For this constraint, the values inserted into good_1.column_2 must always be unique. If the constraint is violated, the database rejects the insertion of the row or the update of good_1.column_2 in an existing row.

  • A simple CHECK constraint named check_1 on good_1.column_3.

    For this constraint, the values inserted into good_1.column_3 must either always be greater than 0 or null. If the constraint is violated, the database rejects the insertion of the row or the update of good_1.column_3 in an existing row.

A constraint that applies either to multiple columns within a base table or to 1 or more columns in another base table is referred to as a table attribute constraint.

Now consider the following table definition.

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 ref_1     FOREIGN KEY (column_5, column_6)
                      REFERENCES parent_1 (column_2, column_3));

Table good_2 defines the following 4 table-level constraints.

  • A composite PRIMARY KEY constraint named primary_1 on column_1 and column_2.

    For this constraint, the values inserted into good_2.column_1 and good_2.column_2 must uniquely identify the row. If the constraint is violated, the database rejects the insertion of the row.

    Because PRIMARY KEY values should not be updated, this constraint is generally not violated, but if it is, the database rejects the update.

  • A composite UNIQUE constraint named unique_1 on column_3 and column_4.

    For this constraint, the values inserted into good_2.column_3 and good_2.column_4 must always be unique within both of those columns. If the constraint is violated, the database rejects the insertion of the row or the update of columns good_2.column_3 or good_2.column_4 in an existing row.

  • A multicolumn CHECK constraint on column_3 and column_4.

    For this constraint, either the value inserted into good_2.column_3 must be greater than zero, or the value inserted into good_2.column_4 must not be null. If both of these constraints is violated, the database rejects the insertion of the row or the update of those columns in an existing row in good_2.

    Because this is constraint is defined using an OR condition, the constraint is satisfied if either good_2.column_3 is greater than zero or good_2.column_4 is not null.

  • A composite FOREIGN KEY constraint on good_2.column_5 and good_2.column_6 that references parent_1.column_2 and parent_1.column_3, with good_2.column_5 mapping to parent_1.column_2 and good_2.column_6 mapping to parent_1.column3.

    For this constraint, if the respective values of good_2.column_5 and good_2.column_6 do not match any pairs of values equal to parent_1.column_2 and parent_1.column_3, the database rejects the insertion of the row into good_2 or the update of an those columns in an existing row in good_2.

    This constraint is also valid when all of columns good_2.column_5, good_2.column_6, parent_1.column_2 and parent_1.column_3 are null, though it is never good design practice to permit any column of a referential integrity constraint to be null.