Oracle constraint to allow particular value once per foreign key value

Question

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

What I want to do is put a constraint on child so that for a particular value of parent_id, there can be only one row with is_initial = 1. There can be any number of rows with is_initial = 0.

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

Thanks.

Answer 1

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.