MySql compound keys and null values

Question

Hi,

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 columns to Not Null and setting default values?

Answer 1

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.