Unique (multiple columns) and null in one column

This works as defined by the SQL standard. NULL means unknown. If you have two records of par_cat = NULL and name=”X”, then the two NULLs are not regarded to hold the same value. Thus they don’t violate the unique key constraint. (Well, one could argue that the NULLs still might mean the same value, but applying this rule would make working with unique indexes and nullable fields almost impossible, for NULL could as well mean 1, 2 or whatever other value. So they did well to define it such as they did in my opinion.)

As MySQL does not support functional indexes where you could have an index on ISNULL(par_cat,-1), name, your only option is to make par_cat a NOT NULL column with 0 or -1 or whatever for “no parent”, if you want your constraints to work.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)