17.05 - Primary Key and Unique Constraints - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
Primary key and unique constraints impose uniqueness on column values amongst the rows of a table.
  • CURRENT VALIDTIME PRIMARY KEY and CURRENT VALIDTIME UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time from current time through the future. Current and future rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
  • SEQUENCED VALIDTIME PRIMARY KEY and SEQUENCED VALIDTIME UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time, including history, current, and future. Any rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
  • NONSEQUENCED VALIDTIME PRIMARY KEY and NONSEQUENCED VALIDTIME UNIQUE constraints treat the valid-time column as a nontemporal column. These constraints ensure that the value for the constrained column in a row is unique amongst all rows in the table. All open rows are prevented from having the same value in the constrained columns.
    Nonsequenced PK/unique constraints are identical to PK/unique constraints on nontemporal tables. These types of constraints are rarely useful on temporal tables, and are not recommended. Due to the near duplication of rows that happens automatically as rows are modified in temporal tables, a nonsequenced PK/unique constraint would be violated very quickly. The same situation is true for USIs applied to temporal tables, which are also not recommended.

Example: Primary key and unique constraints

Assume that the TEMPORAL_DATE is November 2, 2006 (2006/11/02) and a valid-time table is defined with columns: Col1, Col2, and VTCol where VTCol is a valid-time column. Assume further that a CURRENT VALIDTIME UNIQUE constraint is defined on Col2. The following row:

Col1 Col2 (unique) VTCol
5 24 ('2006/10/20', '2007/10/20')

does not violate the constraint with the row:

6 24 ('2008/01/20', '9999/12/31')

because the valid-time periods do not overlap. The same first row would violate the current unique constraint with the row:

7 24 ('2007/09/20', '9999/12/31')

because the time periods overlap from 2007/09/20 to 2007/10/20.

If the table also had a transaction-time as the fourth column, the following row:

Col1 Col2 VTCol TTCol
8 24 ('2008/01/20', '9999/12/31') ('2006/09/20', '2006/09/25')

would not violate the current constraint because the row is closed in transaction time (has an end date prior to UNTIL_CLOSED), so this row is not considered for constraint checking.

Indexes for Primary Key and Unique Constraints

Because of the way rows are duplicated as a result of modifications to temporal tables, most primary key and unique constraints defined on temporal tables are implemented by means of system-defined join indexes (SJIs). These indexes enforce the uniqueness on an appropriate subset of rows, according to whether the constraint is current, sequenced, or nonsequenced.

Example: System-defined join index

A current unique constraint on a bitemporal table causes an SJI to be created and maintained automatically from selected columns of the temporal table. The primary index of the SJI is the constrained column or columns of the temporal table. The valid-time and transaction-time columns are selected using an appropriately qualified WHERE clause that limits the rows in the index to current and future rows:

CREATE JOIN INDEX  tablename_TJI number 
AS SELECT ROWID,  ConstrainedColumn,  VTColumn,  TTcolumnFROM  tablenameWHERE END(VTColumn)  ≥ CURRENT_DATE - INTERVAL '2' DAY
AND   END(TTcolumn) IS UNTIL_CLOSED
PRIMARY INDEX (ConstrainedColumn);

INTERVAL ‘2’ DAY  is required because current rows could be inserted in a time zone that is up to two days prior to the date for the time zone in which the index is created.

SJIs are created in the same database as the constrained temporal table. They are named automatically by the system using a naming convention of tablename_TJI number, where tablename is the first 121 characters of the name of the temporal table for which a PK or unique constraint has been defined, and number is the index ID of the constraint, a unique number that identifies the SJI. Consequently, temporal tables should be named such that the first 121 characters provides per-table uniqueness in the name.

SJIs use the same map for data distribution as the constrained temporal table. If the temporal table uses a sparse map, the SJI also shares the same colocation name as the constrained table. For more information on contiguous and sparse maps, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094.

SJIs are created, maintained, and deleted automatically as needed by the system. They should not be directly modified or deleted.

The PK or unique constraint is not allowed if the associated SJI would cause the maximum permitted number of secondary indexes to be exceeded for the table.

As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used when the SJI was created. Because of this, current and future rows in SJIs, over time, become history rows, and therefore no longer needed in the index to enforce the current constraint.

Use the ALTER TABLE TO CURRENT statement periodically to update SJIs and PPIs created for temporal tables. ALTER TABLE TO CURRENT transitions history rows out the SJIs created for current primary key and unique constraints. For more information on ALTER TABLE TO CURRENT, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Because nonsequenced constraints treat temporal columns as if they were nontemporal, a nonsequenced valid-time PK or unique constraint on a valid-time table is implemented automatically by making the constrained column a USI. For bitemporal tables, a PK or unique constraint must be limited to rows that are open in transaction time, so an SJI is used. The SJI uses a WHERE clause to select only the open rows from the transaction-time column.

Because identity columns are not allowed in join indexes, PK and unique constraints cannot be defined on identity columns in temporal tables.