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

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

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

{ PRIMARY KEY | UNIQUE } (
  column_list [, valid_time_period WITHOUT OVERLAPS ]
)
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.

Usage Notes

  • If valid_time_periodWITHOUT 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);