Postgres add unique constraint

Question

I need to add a constraint to a table x which has many to one relation to other table. So the table x has field other_table_id.

There is other column in table x called primary which is boolean type.

I want to make sure that there is none or only one primary=true per one other_table_id.

Multiple rows can have other_table_id equals some same value and primary=false but only one true per other_table_id.

How do I create this constraint?

user330315 · Accepted Answer · 2013-11-01 09:52:19Z

5

You need a partial unique index for that:

create unique index idx_unique_other 
   on table_x (other_table_id)
   where primary;

This will only index rows where the value of primary column is true. And for those, the other_table_id has to be unique.

answered Nov 1, 2013 at 9:52

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1