Primary Key and Unique Constraints for ANSI Bitemporal Tables | Teradata Vantage - 17.00 - Primary Key and Unique Constraints for ANSI Bitemporal 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

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;