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?
Add a comment
|
2 Answers
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).
Comments
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.
