How to add SQL Constraint to column to check that only 1 row is set to true when the table is groupe-CodePudding

What I would like to do is to add a constraint to a table that can only have the column named primary (which is a bool) set to true only once for a row grouped by 3 columns user_id, seat_id, account_id & where user_challenge status is IN_PROGRESS.

For example this table named users would pass the check constraint:

User_id	seat_id	account_id	status	primary
5	4	3	IN_PROGRESS	false
5	4	3	IN_PROGRESS	true
5	4	3	IN_PROGRESS	false
1	2	7	IN_PROGRESS	true
1	2	7	IN_PROGRESS	false
1	2	7	IN_PROGRESS	false

ALTER TABLE users
ADD CONSTRAINT users_primary_group
CHECK "primary"=true (user_id, seat_id)
    where status = "IN_PROGRESS"

select challenge_id, seat_id, account_id, status
from users
where status = 'IN_PROGRESS'
group by challenge_id, seat_id, account_id,  status

CodePudding user response：

That's a conditional unique constraint:

CREATE UNIQUE INDEX u_user_user_id_seat_id_account_id ON users(
columns user_id
, seat_id
, account_id
)
WHERE status = 'IN_PROGRESS'
AND primary = TRUE;