sql unique index allowing duplicate nulls

Question

I have a table with prize codes

codes
code, user_id, prize_id

I have a unique index on user_id, prize_id

All codes are already in the table and when a user redeems a code a random code with user_id = NULL gets assigned.

Unfortunately this does not work since the unique index does not allow me to add the same prize_id multiple times without assigning a user_id along with it.

Is there some way to tell the unique index that NULL should not be considered a duplicate?

initial state:

code, user_id, prize_id
A, NULL, 1
B, NULL, 1
C, NULL, 1
D, NULL, 2
E, NULL, 2

after user 1 redeems the code for prize 2:

code, user_id, prize_id
A, NULL, 1
B, NULL, 1
C, NULL, 1
D, 1, 2
E, NULL, 2

NULL is not a value, it's the absence of one. Maybe make an ID called "No Prize" as the default. — Jacob H, Dec 28 '17 at 19:39
@JacobH Then I still have the problem that this will conflict with the unique index — Chris, Dec 28 '17 at 19:41
Does this help? https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls — Chris Mack, Dec 28 '17 at 19:42
Remove the unique index on user_id if its a nullable column in your data model — Dranes, Dec 28 '17 at 19:43

score 1 · Accepted Answer · answered Dec 28 '17 at 19:43

1

Just use a filtered index:

create unique index unq_codes_user_prize on codes(user_id, prize_id) 
    where user_id is not null;

answered Dec 28 '17 at 19:43

Gordon Linoff

1,242,037
58
646
786

sql unique index allowing duplicate nulls

1 Answers1