A column or column set is the primary key for table_name. The defined column set makes each row in the table unique. The primary key is also used to enforce referential constraints.
The column must be defined as NOT NULL.
- column attribute for a single column or a column set
- table attribute, except for volatile tables
- BLOB
- BLOB UDT
- CLOB
- CLOB UDT
- VARIANT_TYPE
- ARRAY
- VARRAY
- Period
- XML
- Geospatial
- JSON
- DATASET
A table can only have one primary key. To specify candidate primary keys for referential integrity relationships with other tables, use the UNIQUE column attribute. This is not necessary for Referential Constraints, but is required for standard referential integrity constraints and batch referential integrity constraints.
PRIMARY KEY constraints are valid for nontemporal and temporal tables. For information about temporal tables and temporal syntax, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.
For a PRIMARY KEY constraint, nontemporal tables use a system-defined secondary index and temporal tables use a single-table join index. System-defined secondary or single-table join indexes used for this constraint count toward a maximum of 32 secondary, hash, and join indexes per table.
Like UNIQUE constraints, PRIMARY KEY constraints ensure that the uniqueness of alternate keys is enforced when they are specified in a referential integrity relationship.
When a PRIMARY KEY constraint is defined for a normalized table, Vantage validates the constraint with normalized rows. If the normalized row violates the PRIMARY KEY constraint, the system returns an error to the requestor.
For an unnamed PRIMARY KEY column constraint, use this syntax:
PRIMARY KEY
- CONSTRAINT constraint_name
- For a named PRIMARY KEY column constraint, use this syntax:
CONSTRAINT constraint_name PRIMARY KEY
Example: 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));
Example: Specifying a Mix of Column-Level 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));