I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables.
http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS1005
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html
Should primary keys be added to these tables?
I'm asking because I'm wondering about how one of these temporal tables should be referenced by another table. Can I add a foreign key from one temporal table to another?
If I add a pk/ foreign key realtionship, and then update the referenced in the table with the pk, the table with the fk points to a record that is no longer relevant…. does temporal data break normal primary key – foreign key relationships? And if so, how does this affect performance, do I just use a normal column as the 'foreign key' and select the right one for a referenced time period in a query?
Does anybody know of or have any examples or tutorials handy that show temporal data with normal or pseudo-normal pk / fk usage?
Thanks
Best Answer
Unfortunately present SQL or its implementations in all RDBMS products does not fully support temporal relations.
Only Teradata and DB2 have some features implementing constraints having time in the two or more of columns.
My example:
SET SCHEMA TEST;
CREATE TABLE PRODUCT (
product_ID INT NOT NULL,
suplier NVARCHAR(50),
PRIMARY KEY (product_ID)
) ;
CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID)
)
;
ALTER TABLE CAMPAIGN
ADD CONSTRAINT XFK_campaign_productid
FOREIGN KEY (product_ID) REFERENCES PRODUCT (product_id)
ON DELETE NO ACTION;
If you add temporal columns business_startdate and business_enddate and optionally
transaction_starttime and transaction_endtime, you cannot enforce referential integrity anymore.
You might have to implement triggers or stored procedures or application level coding if you want to implement a constraint like this:
CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
transaction_starttime TIMESTAMP NOT NULL,
transaction_endtime TIMESTAMP NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID,business_startdate,transaction_starttime)
)
;
For DB2 there exist a temporal primary key constaint in the following syntax:
CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
price DECIMAL,
PERIOD BUSINESS_TIME (business_startdate,business_enddate),
PRIMARY KEY (campaign_ID,BUSINESS_TIME WITHOUT OVERLAPS)
)
;