Create a PostgreSQL constraint to prevent unique combination rows

Question

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.

Answer 1

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.)

• Evan Carroll: is_active can't be boolean, ERROR: data type boolean has no default operator class for access method "gist"
• ypercubeᵀᴹ: @EvanCarroll I can't remember how well I tested this when I posted. But it works with int and smallint.
• ypercubeᵀᴹ: 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 ;)
• Evan Carroll: 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.
• LondonRob: 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.

Answer 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.