Imagine you have a simple table:
name | is_active
----------------
A | 0
A | 0
B | 0
C | 1
... | ...
I need to create a special unique constraint which fails on following situation:
different is_active
values can't co-exist for the same name
value.
Example of permitted condition:
Note: simple multi-column unique index won't permit combination like this.
A | 0
A | 0
B | 0
Example of permitted condition:
A | 0
B | 1
Example of failed condition:
A | 0
A | 1
-- should be prevented, because `A 0` exists
-- same name, but different `is_active`
Ideally, I need unique constraint or unique partial index. Triggers are more problematic for me.
Double A,0
allowed, but (A,0) (A,1)
isn't.
Best Answer
You can use an exclusion constraint with
btree_gist
,Then we add a constraint that says:
"We can't have 2 rows that have the same
name
and differentis_active
":Some notes:
is_active
can be integer or boolean, makes no difference for the exclusion constraint. (actually it does, if the column is boolean you need to use(is_active::int) WITH <>
.)name
oris_active
is null will be ignored by the constraint and thus allowed.UNIQUE
constraint on(name)
alone would be easier and more appropriate. I don't see any reason for storing multiple identical rows.name = 'A'
and you want to to update is_active status from 0 to 3, all 1000 will have to be updated. You should examine whether normalizing the design would be more efficient. (Normalizing meaning in this case to remove is_active status from the table and add a 2-column table with name, is_active and a unique constraint on(name)
. Ifis_active
is boolean, it could be totally stripped and the extra table just a single column table, storing only the "active" names.)