10

Using InnoDB/MySQLi, I have a simple table: mytable. The table has four fields: id (primary, auto_inc), field1, field2, field3. All of them are BIGINT and, except for id, can be NULL.

I have added a unique constraint like so:

ALTER TABLE mytable ADD UNIQUE INDEX(field1,field2,field3);

However, I am perfectly able to add the following rows without any error being generated. I would like for this to generate a 'duplicate' error, but it doesn't:

INSERT INTO mytable VALUES (NULL,3,NULL)
INSERT INTO mytable VALUES (NULL,3,NULL)

It only generates a 'duplicate' error if all of the fields have non-NULL values - e.g.,

INSERT INTO mytable VALUES (2,3,4)
INSERT INTO mytable VALUES (2,3,4)

How can I tell MySQL to generate 'duplicate' errors even if one (or more) of the fields have NULL values?

EDIT: This was previously added as a "bug" to MySQL: http://bugs.mysql.com/bug.php?id=25544

a.real.human.being
  • 878
  • 2
  • 6
  • 17

3 Answers3

5

You can't compare NULL's (if you compare anything with NULL even NULL=NULL the results is always FALSE) this behavior is documented in MySQL ref.

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. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

So I think the only way is define columns NOT NULL or handle this issue in a trigger.

a.real.human.being
  • 878
  • 2
  • 6
  • 17
valex
  • 23,966
  • 7
  • 43
  • 60
2

The root of the problem is - in comparison of NULL-s. You should understand logical meaning of NULL. And it is "no value". Not "zero value" or "unknown value", but "no value". That's a big difference.

That is why making part of unique index NULL-able is certainly bad idea. You can not compare NULL's as you can not compare two values, both of which are absent. Thus, DBMS can not maintain NULL-s to be unique as far as comparison isn't applicable to them in normal way. Yes, such things as <=> operator exist in MySQL (or IS NULL in other DBMS) - but that is about technical resolution of how to deal with comparisons with NULL values - but not the logical.

So you're in the middle of XY-problem. Do not use NULL-s with unique keys - they can not be there by definition of what is NULL and what is intention of unique key. And from the technical viewpoint (see part about index creation), NULL=NULL will always result in false - thus, it's permitted to insert NULL value if another NULL-value exists.

Community
  • 1
  • 1
Alma Do
  • 37,009
  • 9
  • 76
  • 105
1

Go to: How to Use Unique Indexes in MySQL and Other Databases and see the "MySQL NULLs" section.

Bartosz Polak
  • 136
  • 6
  • If this is correct, then the answer is that it cannot be done - I simply cannot use fields that can be NULL in a unique index and avoid the problem I'm having. In the future, your contribution to the question (i.e., simply providing a link) would have been better off as a comment, not an answer. – a.real.human.being Feb 11 '14 at 07:57
  • MySQL treats NULL values in this case some like empty value which can't be compared to another value, so it won't throw any exception. I would provide comment instead of answer, but I'm 14pts. short for that at a time ;-D – Bartosz Polak Feb 11 '14 at 08:03
  • Apologies, I had forgotten that you needed a certain reputation level to add comments :) – a.real.human.being Feb 11 '14 at 08:11