15.10 - Primary Key and Unique Constraints for ANSI Bitemporal 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  

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” on page 55.

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;