8

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.

16

You can use an exclusion constraint with btree_gist,

-- This is needed
CREATE EXTENSION btree_gist;

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
    EXCLUDE  USING gist
    ( 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
2

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 $$
declare
    active int;
begin
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.