11

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.

Improve this question
0

2 Answers 2

Reset to default
19

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.)
Improve this answer
5
  • is_active can't be boolean, ERROR: data type boolean has no default operator class for access method "gist" Commented Jun 9, 2017 at 18:54
  • 1
    @EvanCarroll I can't remember how well I tested this when I posted. But it works with int and smallint. Commented Jun 9, 2017 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 ;) Commented Jun 9, 2017 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. Commented Jun 9, 2017 at 19:26
  • The Postgres docs have a nice example of this in action, where a zoo cage can only contain one type of animal. Worth adding to the solution maybe. Commented Aug 19, 2020 at 12:39
3

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.