Primary Key and Unique Constraints for ANSI Valid-Time Tables | Teradata Vantage - 17.00 - Primary Key and Unique Constraints for ANSI Valid-Time Tables - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

Valid-time table definitions can include primary key and unique constraints that prevent rows from having valid-time periods that overlap. A system-defined join index is created automatically for constraint checking during modifications to the table.

Syntax

{ PRIMARY KEY | UNIQUE } (
  column_list [, valid_time_period WITHOUT OVERLAPS ]
)
column_list
The column name or the comma-separated list of columns that serve as the primary key or uniqueness constraint for the table.
valid_time_period
The name of the valid-time derived period column.
WITHOUT OVERLAPS
Specifies that valid-time periods of the table rows cannot overlap.

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

Usage Notes

  • If valid_time_periodWITHOUT OVERLAPS is not specified, the constraint acts as a nontemporal constraint and imposes uniqueness on the values disregarding whether valid-time periods overlap.
  • If the table includes a SYSTEM_TIME derived period column, the start and end column components of the derived period column cannot be included in the column_list.

Example: Temporal Primary Key Constraint on a Valid-Time Table

CREATE MULTISET TABLE employee_vt_pk (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL,
 PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME,
 PRIMARY KEY(deptno, job_dur WITHOUT OVERLAPS)
 )
 PRIMARY INDEX (eid);