Could you please share me some light regarding to primary key operation on Table with Temporal Validity in Oracle?
I have created an table with following schema
Create table TemporalTable_1 (
Customer_ID number(8),
Customer_name varchar2(100),
valid_period_start timestamp,
valid_period_end timestamp,
period for valid_period(valid_period_start, valid_period_end),
constraint TemporalTable_1_PK primary key (Customer_ID , VALID_PERIOD)
)
I have following records from another table "OtherTable" and I need to copy into the TemporalTable_1
Customer_ID | Customer_name | Valid_period_start | Valid_Period_end ------------------+----------------------+-------------------------+----------------------- 00001 | John Chan | 01 JUN 2020 00:00:00 | 09 JUN 2020 23:59:59 00001 | Johnny Chan | 10 JUN 2020 00:00:00 | Null
Following is my script:
insert into TemporalTable_1 select * from OtherTable;
ORA-00001: unique constraint (TemporalTable_1) violated
Before execute the insert statement, the table was blank. So my question is why I am not allowed copy the row into the TemporalTable_1 even the rows have different valid_period.
Is it because Oracle actually didn't care about the valid period column on the primary key?
Thanks in advance!
Period name(VALID_PERIOD in your case) columns contains just ID of your period. Check this script and its' output:
--drop table TemporalTable purge;
-- formatting for sqlplus:
col periodname for a20;
col constraint_name for a20;
col search_condition_vc for a80;
col valid_period_start for a16;
col valid_period_end for a16;
col PERIODSTART for a20;
col PERIODEND for a20;
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi';
-- end of formatting
Create table TemporalTable (
Customer_ID number(8),
Customer_name varchar2(10)
);
alter table TemporalTable add (
valid_period_start timestamp,
valid_period_end timestamp,
period for valid_period(valid_period_start, valid_period_end)
);
ALTER TABLE TemporalTable ADD (
vt_start DATE,
vt_end DATE,
PERIOD FOR vt (vt_start, vt_end)
);
-- inserting overlapping records:
insert into TemporalTable
select 1, 'A' , date'2020-01-01', date'2020-01-10', date'2020-01-01', date'2020-01-10' from dual union all
select 1, 'B' , date'2020-01-05', date'2020-01-08', date'2020-01-01', date'2020-01-10' from dual
/
commit;
-- check valid_period and vt hidden columns:
select tt.*,valid_period,vt from TemporalTable tt
/
select *
from TemporalTable
AS OF PERIOD FOR valid_period DATE '2020-01-06'
/
select
constraint_name,
constraint_type,
search_condition_vc
from user_constraints c
where table_name='TEMPORALTABLE';
select * from sys.SYS_FBA_PERIOD
where obj#=(select object_id from user_objects where object_name='TEMPORALTABLE');As you can see I added 2 validity periods: VALID_PERIOD as in your case and VT. Notice that hidden columns VALID_PERIOD and VT contains just their ID. They are the same for all rows in the table. Moreover in the following presentation by Philipp Salvisberg you can see that Temporal Validity in Oracle doesn't support yet temporal integrity constraints yet and moreover it doesn't even support checks for overlapping periods. So you can't create such constraints yet. But you can read old methods for such things (for example, like foreign key constraints START_DATE -> prev(END_DATE) and unique contraint on ID, START_DATE)
Output:
CUSTOMER_ID CUSTOMER_N VALID_PERIOD_STA VALID_PERIOD_END VT_START VT_END VALID_PERIOD VT
----------- ---------- ---------------- ---------------- ------------------- ------------------- ------------ ----------
1 A 2020-01-01 00:00 2020-01-10 00:00 2020-01-01 00:00:00 2020-01-10 00:00:00 726847999 726848005
1 B 2020-01-05 00:00 2020-01-08 00:00 2020-01-01 00:00:00 2020-01-10 00:00:00 726847999 726848005
CUSTOMER_ID CUSTOMER_N VALID_PERIOD_STA VALID_PERIOD_END VT_START VT_END
----------- ---------- ---------------- ---------------- ------------------- -------------------
1 A 2020-01-01 00:00 2020-01-10 00:00 2020-01-01 00:00:00 2020-01-10 00:00:00
1 B 2020-01-05 00:00 2020-01-08 00:00 2020-01-01 00:00:00 2020-01-10 00:00:00
CONSTRAINT_NAME C SEARCH_CONDITION_VC
-------------------- - --------------------------------------------------------------------------------
VALID_PERIOD52D1FF C (VALID_PERIOD_START < VALID_PERIOD_END) and (VALID_PERIOD > 0)
VT52D205 C (VT_START < VT_END) and (VT > 0)
OBJ# PERIODNAME FLAGS PERIODSTART PERIODEND SPARE
---------- -------------------- ---------- -------------------- -------------------- ----------
89220 VALID_PERIOD 0 VALID_PERIOD_START VALID_PERIOD_END
89220 VT 0 VT_START VT_END