I have a situation where a department has many cases, but only 1 case can be active.

In the cases table, there is are department_id: bigint references departments.id and active: boolean columns. How can I set up a unique constraint so that a department could have 3 cases, but only 1 can ever be marked as active: TRUE?

CodePudding user response：

You can use a partial unique constraint:

create unique index on cases (department_id)
where active

CodePudding user response：

You can have 2 simple posibilities:

Make the boolean have only two posibilities: true and null and then set a unique constraint for (department_id, active)
If you can't have only true and null set another column (in a trigger or a calculated column) with the true/null value based in true/false/null column.
Add an unique index over the expression

unique over expresion

create unique index "only one active por department" on cases
  (department_id, (case active when true then true else null end));

Living example: https://onecompiler.com/postgresql/3y7fe9hye

example of calculated column

For alternative 2:

select version();

create table cases(
  department_id integer,
  active boolean,
  value text
);

insert into cases values 
  (1, true, 'a'),
  (1, false, 'b'),
  (1, false, 'c'),
  (2, true, 'd'),
  (2, false, 'e'),
  (2, null, 'f');

-- select * from cases;

alter table cases 
  add column active_true boolean GENERATED ALWAYS 
    AS (case active when true then true else null end) stored;
    
alter table cases
  add unique (department_id, active_true);
  
-- ok:
insert into cases (department_id, active, value) values (1,false, 'g'); 

select * from cases;  

-- fails:
insert into cases (department_id, active, value) values (1,true , 'h'); 

select * from cases;

obtains:

version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.3 (Debian 14.3-1.pgdg110 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

CREATE TABLE
INSERT 0 6
ALTER TABLE
ALTER TABLE
INSERT 0 1
 department_id | active | value | active_true 
--------------- -------- ------- -------------
             1 | t      | a     | t
             1 | f      | b     | 
             1 | f      | c     | 
             2 | t      | d     | t
             2 | f      | e     | 
             2 |        | f     | 
             1 | f      | g     | 
(7 rows)


psql:commands.sql:32: ERROR:  duplicate key value violates unique constraint "cases_department_id_active_true_key"
DETAIL:  Key (department_id, active_true)=(1, t) already exists.

Live example: https://onecompiler.com/postgresql/3y7exvrvq