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
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;