POSTGRES : Add a constraint over rows

Question

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.

@BartoszOlchowik: No need for, too complex for such a simple thing as a conditional unique constraint — Frank Heikens
– Frank Heikens, Commented Nov 21, 2022 at 19:49

Frank Heikens · Accepted Answer · 2022-11-21 19:47:11Z

3

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

answered Nov 21, 2022 at 19:47

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pranil Ambule Over a year ago

Thanks, it worked! I was making the index on combination of userId and state. CREATE UNIQUE INDEX u_user_id_card_state ON card(userId) WHERE cardState IN('ACTIVE','LOCKED'); which is why it didn't work.

Collectives™ on Stack Overflow

POSTGRES : Add a constraint over rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related