POSTGRES : Add a constraint over rows-CodePudding

I have a card table where I need to have a constraint, when a user has a card in ACTIVE or LOCKED state a new card shouldn't be created.

CREATE TABLE card
(cardId int,
userId int,
cardState varchar(255));

I tried exploring exclusion using gist exclusion and partial unique index but couldn't make any progress. Any help would be appreciated.

CodePudding user response：

Use a conditional unique constraint/index:

CREATE TABLE card
(cardId int,
userId int,
cardState varchar(255)); 

CREATE UNIQUE INDEX u_user_id_card_state ON card(userId) WHERE cardState IN('ACTIVE','LOCKED');

INSERT INTO card(cardid, userid, cardstate) VALUES(1,1, 'ACTIVE');

INSERT INTO card(cardid, userid, cardstate) VALUES(1,1, 'ACTIVE'); -- fails

INSERT INTO card(cardid, userid, cardstate) VALUES(1,1, 'LOCKED'); -- fails

INSERT INTO card(cardid, userid, cardstate) VALUES(1,1, 'something else'); -- works