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
Language
English (United States)
Last Update
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);