Oracle constraint to provide a specific value once per foreign key value

Question

Let's say I have a parent table with primary key id and a child table with foreign key parent_id and column "boolean" (with restrictions on 0 or 1), call it is_initial .

I want to make a restriction on child , so for a specific value of parent_id can only be one line with is_initial = 1 . There can be any number of lines with is_initial = 0.

Can this be done with a restriction? I prefer not to add a trigger.

Thanks.

Answer 1

Here you go, I believe that I understand what you are looking for now

Note the change in the unique index:

 create unique index childTable_initialIndex on childTable( case when is_initial = 1 then parent_id else null end); 

Corrected Code

 create table childTable(parent_id number, child_id number primary key, is_initial number, somethingelse varchar2(50) ); create unique index childTable_initialIndex on childTable( case when is_initial = 1 then parent_id else null end); insert into childTable(parent_id, child_id, is_initial,somethingelse) values (1,1,0,'works'); 1 rows inserted. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (1,1,0,'will not work if childId is pk'); SQL Error: ORA-00001: unique constraint (SYS_C0062138) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key insert into childTable(parent_id, child_id, is_initial,somethingelse) values (1,2,1,'works3'); 1 rows inserted. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (1,3,1,'should not work'); SQL Error: ORA-00001: unique constraint (CHILDTABLE_INITIALINDEX) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (2,4,0,'works4'); 1 rows inserted. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (2,5,0,'works5'); 1 rows inserted. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (2,6,1,'works6'); 1 rows inserted. insert into childTable(parent_id, child_id, is_initial,somethingelse) values (2,7,1,'should not work'); SQL Error: ORA-00001: unique constraint (CHILDTABLE_INITIALINDEX) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. --we should only see things that work select * from childTable / --this should not work, since works already has the 1/1 is_initial 1 update childTable set somethingelse = 'Should not work!' , is_initial = 1 where somethingelse = 'works'; SQL Error: ORA-00001: unique constraint (CHILDTABLE_INITIALINDEX) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. 

Here are the results:

 PARENT_ID CHILD_ID IS_INITIAL SOMETHINGELSE --------- -------- ---------- -------------------------------------------------- 1 1 0 works 1 2 1 works3 2 4 0 works4 2 5 0 works5 2 6 1 works6 

Answer 2

You can do this with a unique index:

 create unique index initialindex on child( case when is_initial <> 1 then parent_id || 'xx' || child_id else null end ); 

Now, after trying to insert a second line with is_initial = 1, you should get a constraint violation.

Answer 3

Do it in a more "relational" way - don't use child.is_initial , but use parent.initial_child_id , which can be NULL, and is a FOREIGN KEY in the child table.

Since initial_child_id is in the parent table, and not in the child , it is natural that there can only be one for each parent.

Your DDL will look something like this:

 CREATE TABLE parent ( id INT, initial_child_id INT, PRIMARY KEY (id) ); CREATE TABLE child ( child_id INT, parent_id INT NOT NULL, PRIMARY KEY (child_id) ); ALTER TABLE parent ADD FOREIGN KEY (initial_child_id) REFERENCES child; ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent;