Oracle Partial Foreign Key that is not unique

Question

I have some problems with Oracle Foreign Composite keys. I have an application that is somewhat big (you know, 5000+ tables, that kind of thing) and we store some 'static' (it actually can change) data into some set of tables. This data is referenced by a lot of the tables through the database, so it worked like this:

TABLE StaticData 
ID(PK)  Data    
1       StaticData1
2       StaticData2
...
n       StaticDataN

TABLE TypicalTable
ID(PK)  StaticDataID(FK to StaticData)  
1       1
2       1
3       7
4       2
...
n       n

And all was well in Wonderland.

But some changes of spec, and some meetings with the client after, we were tasked with having different 'versions' of the data ready to replace the static data when some time arrives. Last part was easy, we can create jobs that will check every day/week for a date and change the data, but we will have to maintain older and newer versions of the data... in the same table. So now StaticData looks like:

TABLE StaticData 
ID(PK)  Data            KickInDate(Also PK)
1       StaticData1.1       01/01/1900
1       StaticData1.2       10/07/2014
1       StaticData1.3       12/12/2015
2       StaticData2.1       01/01/1900
...
n       StaticDataN.1       01/01/1900

And of course all integrity reference has gone off the board. And of course, since I cannot put a UNIQUE constraint in the ID, I cannot keep the foreign keys.

I have searched the net for a solution for this (less restrictive kind of foreign keys) and most of the time the solution is to use triggers checking BEFORE INSERT|UPDATE|DELETE

But that will be kind of a very, very, very big job.

So I ask, Do I have other solutions? Is there any way to tell Oracle to reference another column of another table even thought is not UNIQUE? (it will definitely be NOT NULL).

Answer 1

Primary keys in Oracle can have duplicates. Primary keys can be built with non-unique indexes and existing values can be excluded by creating the constraint with NOVALIDATE. It is a rarely used feature, will confuse people, and is not a clean solution. But in the real world sometimes data isn't clean and there's no time for the perfect solution.

Sample schema and data.

create table staticData
(
    id number not null,
    data varchar2(100),
    constraint staticData_pk primary key (id)
);

create table typicalTable
(
    id number not null,
    staticDataID number,
    constraint typicalTable_pk primary key (id),
    constraint typicalTable_fk foreign key (staticDataID)
        references staticData(id)
);

insert into staticData values (1, 'StaticData1');
insert into staticData values (2, 'StaticData2');

insert into typicalTable values(1, 1);
insert into typicalTable values(2, 1);

Process to drop constraints, add duplicate data, and re-enable constraints.

--Drop constraints.
alter table typicalTable drop constraint typicalTable_fk;
alter table staticData drop constraint staticData_pk;

--Add semi-duplicate data.
insert into staticData values (1, 'StaticData1.2');

--Use a non-unique index to build a NOVALIDATE primary key.
create index staticData_pk on staticData(id);
alter table staticData add constraint staticData_pk primary key (id) novalidate;
alter table typicalTable add constraint typicalTable_fk foreign key(staticDataID)
    references staticData(id);