Oracle constraint to allow particular value once per foreign key value

You can do it 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 you try to insert a second row with is_initial = 1 you should get a constraint violation.

Here you go, I believe 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);

Ammended 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) violated00001. 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) violated00001. 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) violated00001. 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 1update childTable    set somethingelse = 'Should not work!'     , is_initial    = 1 where somethingelse = 'works';SQL Error: ORA-00001: unique constraint (CHILDTABLE_INITIALINDEX) violated00001. 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  

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

Since initial_child_id is in the parent table and not in the child, there can naturally be only one per parent.

Your DDL would 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;