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

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  

Syntax Element	Description
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 is ANSI SQL:2011 compliant.

Usage Notes  

If valid_time_period WITHOUT 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);