Add a deferrable unique constraint with a check

Asked Mar 21 '22 at 15:55

Active Mar 22 '22 at 08:27

Viewed 178 times

I would like to add a deferrable unique constraint with a check, such as

ALTER TABLE locations 
   ADD CONSTRAINT short_name 
    UNIQUE (hallway, row, module, level, position, zone_id) 
    CHECK (active) DEFERRABLE

But it is not possible.

Context: I have a table in which a given combination of fields must be unique, just when the field active is set on true (Possible CHECK?). Normally we receive a list of updates that must be performed as an atomic transaction, that is why the constraint must be deferrable.

The answer to this question shows as an option one UNIQUE INDEX, but it is not deferrable.

Is there any possible solution or workaround?

edited Mar 22 '22 at 08:27

asked Mar 21 '22 at 15:55

Diego Muñoz Corrales

You can't combine a UNIQUE constraint and a CHECK constraint. – Mar 21 '22 at 15:57
@a_horse_with_no_name Exactly that is my question, which workarounds exist? – Diego Muñoz Corrales Mar 21 '22 at 16:00
The only possibility is a deferred constraint trigger with `SERIALIZABLE` isolation level. – Laurenz Albe Mar 21 '22 at 16:12

Add a deferrable unique constraint with a check

0 Answers0