Primary Key and Unique Constraints for ANSI Bitemporal Tables | Teradata Vantage - 17.10 - Primary Key and Unique Constraints for ANSI Bitemporal Tables - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

Like valid-time table definitions, bitemporal table definitions can include primary key and unique constraints that prevent rows from having valid-time periods that overlap.

Syntax

The syntax for adding a primary key or unique constraint to bitemporal tables is the same as the syntax used for valid-time tables, described in Primary Key and Unique Constraints for ANSI Valid-Time Tables.

Usage Notes

  • The system-time derived period column cannot be included in the columns that constitute the primary key or unique constraint.
  • The constraint applies only to rows that are open in the system-time dimension.

Example: Temporal Primary Key Constraint on an ANSI Bitemporal Table

CREATE MULTISET TABLE employee_bitemp_pk (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 deptno INTEGER 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,
 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW START,
 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
 PRIMARY KEY(deptno,job_dur WITHOUT OVERLAPS)
 )
 PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;