CODEWITHSUNDEEP
sqlpostgresql
NoDisplayName
NoDisplayName

Reputation: 15736

PostgreSQL unique index for a specific set of values

Let's say there is a table AVATARS and among other columns it has user_id (foreign key to users table) and state where state can be any value from 0 to 6. I want to create a constraint so that user can have only 2 avatars, where one has state = 0 and the other has any state != 0. I wonder if it's possible to implement with a unique index? Or I am gonna have to add some boolean column?

Upvotes: 1

Views: 70

Answers (2)

jcaron
jcaron

Reputation: 17710

You could use a unique index with an expression:

CREATE UNIQUE INDEX avatars_userid_state_0_idx ON avatars(user_id,(state<>0));

(or state = 0, the end result is the same).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can do this using filtered unique indexes.

For one value with state = 0:

create unique index unq_avatars_user_state0
    on avatars(user_id, state)
    where state = 0;

For one value with state <> 0:

create unique index unq_avatars_user_state0
    on avatars(user_id, state)
    where state <> 0;

Note: these do not guarantee that such values exist, just that there is at most one of each them.

Upvotes: 0

Related Questions