Don't put nullable fields in your MySQL unique keys

MySQL will allow you to include a nullable field in a unique key. I heartilly recommend that you don't do it.

You may already be aware that NULL isn't equal to anything. That's why we always have 'IS NULL' instead of '= NULL' in our where clauses. When they say not equal to anything, they really mean not equal to anything. Not even itself.

So let's say you have a table that looks a bit like this:

always1	always2	always3	sometimes1	sometimes2
a	b	c	d	e
f	g	h	NULL	NULL
i	value1	value2	NULL	NULL

and you have a unique key on always2, always3, and sometimes1

You build some logic around the fairly reasonable idea 'we'll try to insert, and if we get a duplicate key error, we'll update instead'.

And then you try to insert
'always2 = "value1", always3 = "value2", sometimes1 = NULL, always1 = m, sometimes2 = n'
expecting this to fail because of a duplicate key error and update the values for always1 and sometimes2 for the third row above. But it won't. Because NULL isn't equal to itself. So you'll end up with a table like this:

always1	always2	always3	sometimes1	sometimes2
a	b	c	d	e
f	g	h	NULL	NULL
i	value1	value2	NULL	NULL
m	value1	value2	NULL	n

Beware!