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

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

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