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;