MySql compound keys and null values

I have noticed that if I have a unique compound keys for two columns, column_a and column_b, then my sql ignores this constraint if one column is null.

E.g.

if column_a=1 and column_b = null I can insert column_a=1 and column_b=null as much as I like

if column_a=1 and column_b = 2 I can only insert this value once.

Is there a way to apply this constraint, other than maybe changing the col开发者_JS百科umns to Not Null and setting default values?

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

"A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL."

So, no, you can't get MySQL to treat NULL as a unique value. I guess you have a couple of choices: you could do what you suggested in your question and store a "special value" instead of null, or you could use the BDB engine for the table. I don't think this minor difference in behaviour warrants making an unusual choice of storage engine, though.

I worked around this issue by creating a virtual (stored) column on the same table that was COALESCE(column_b, 0). I then made by unique composite index based upon that column (and the second column) instead. Works very well.

Of course this was probably not possible back in 2010 :)