The ability to track Transaction Time (TT) and Valid Time (VT) is a straight-forward implementation of the temporal and bi-temporal database. However, the ability to enforce temporal continuity of Foreign Key and Unique Key constraints is an important aspect of maintaining temporal continuity within that database. This whitepaper provides definitions and examples of temporal continuity enforcement in a temporal database implementation of DDGS
If temporal constraints are enforced on primary keys, the same primary key could be valid during 2 different time frames. However, Oracle 21c will not allow this situation.
select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
DROP TABLE DEPT;
Table DEPT dropped.
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
,DNAME VARCHAR2(14)
,LOC VARCHAR2(13)
,PERIOD FOR dept_track_time
);
Table DEPT created.
SELECT SUBSTR(COLUMN_NAME,1,22) NAME
,SUBSTR(DATA_TYPE,1,28) DATA_TYPE
,COLUMN_ID AS COL_ID
,SEGMENT_COLUMN_ID AS SEG_COL_ID
,INTERNAL_COLUMN_ID AS INT_COL_ID
,HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'DEPT';
NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HID
---------------------- ---------------------------- ---------- ---------- ---------- ---
DEPT_TRACK_TIME_START TIMESTAMP(6) WITH TIME ZONE 1 1 YES
DEPT_TRACK_TIME_END TIMESTAMP(6) WITH TIME ZONE 2 2 YES
DEPT_TRACK_TIME NUMBER 3 YES
DEPTNO NUMBER 1 3 4 NO
DNAME VARCHAR2 2 4 5 NO
LOC VARCHAR2 3 5 6 NO
6 rows selected.
INSERT INTO DEPT (DEPT_TRACK_TIME_START, DEPT_TRACK_TIME_END, DEPTNO, DNAME, LOC)
VALUES (systimestamp-5, systimestamp-4, 10, 'ACCOUNTING','NEW YORK');
1 row inserted.
INSERT INTO DEPT (DEPT_TRACK_TIME_START, DEPT_TRACK_TIME_END, DEPTNO, DNAME, LOC)
VALUES (systimestamp-1, systimestamp-0, 10, 'ACCOUNTING','NEW YORK');
Error:
INSERT INTO DEPT (DEPT_TRACK_TIME_START, DEPT_TRACK_TIME_END, DEPTNO, DNAME, LOC)
VALUES (systimestamp-1, systimestamp-0, 10, 'ACCOUNTING','NEW YORK')
Error report -
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated