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.

migrated from stackoverflow.com Jan 18 '17 at 18:23

This question came from our site for professional and enthusiast programmers.


You can use an exclusion constraint with btree_gist,

-- This is needed

Then we add a constraint that says:

"We can't have 2 rows that have the same name and different is_active":

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    ( name WITH =, 
      is_active WITH <>      -- if boolean, use instead:
                             -- (is_active::int) WITH <>

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 <>.)
  • Rows where name or is_active is null will be ignored by the constraint and thus allowed.
  • The constraint makes sense only if the table has more columns. Otherwise, if the table has only these 2 columns, a UNIQUE constraint on (name) alone would be easier and more appropriate. I don't see any reason for storing multiple identical rows.
  • The design violates 2NF. While the exclusion constraint will save us from update anomalies, it may not from performance issues. If you have for example 1000 rows with 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). If is_active is boolean, it could be totally stripped and the extra table just a single column table, storing only the "active" names.)
  • is_active can't be boolean, ERROR: data type boolean has no default operator class for access method "gist" – Evan Carroll Jun 9 '17 at 18:54
  • 1
    @EvanCarroll I can't remember how well I tested this when I posted. But it works with int and smallint. – ypercubeᵀᴹ Jun 9 '17 at 18:59
  • Also works using EXCLUDE USING gist (name WITH =, (is_active::int) WITH <>) if it's boolean. And the question has 0 and 1, not true and false so it's rather unlikely I tested with booleans ;) – ypercubeᵀᴹ Jun 9 '17 at 19:01
  • All good, I used an exclusion constraint over dba.stackexchange.com/a/175922/2639 and I had a problem using a booleans so I went searching. I thought btree_gist covered bools but it doesn't. – Evan Carroll Jun 9 '17 at 19:26

This is not a case where you can use a unique index. You can test the condition in a trigger, e.g.:

create or replace function a_table_trigger()
returns trigger language plpgsql as $$
    active int;
    select is_active into active
    from a_table
    where name = new.name;

    if found and active is distinct from new.is_active then
        raise exception 'The value of is_active for "%" should be %', new.name, active;
    end if;
    return new;
end $$;

Test it here.

Your Answer

