Bug #8173 unique index allows duplicates with null values
Submitted: 28 Jan 2005 5:44 Modified: 28 Jan 2005 6:20
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0-alpha-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[28 Jan 2005 5:44] [ name withheld ]
Description:
unique index allows duplicates if at least one of the columns is null.

How to repeat:
mysql> create table tb
    -> (
    ->  id      int not null auto_increment,
    ->  a       int,
    ->  b       int,
    ->  primary key (id),
    ->  index (a),
    ->  index (b),
    ->  unique index (a,b)
    -> ) type = innodb; /* occurs in myISAM tables as well */
Query OK, 0 rows affected, 1 warning (0.42 sec)

mysql> insert into tb (a,b) values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb (a,b) values (1,2);       /* fails (correct) */
ERROR 1062 (23000): Duplicate entry '1-2' for key 2

mysql> insert into tb (a,b) values (1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb (a,b) values (1,null);    /* succeeds (incorrect?) */
Query OK, 1 row affected (0.02 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.0-alpha-nt |
+----------------+
1 row in set (0.00 sec)
[28 Jan 2005 6:20] MySQL Verification Team
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From the MySQL manual:

In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL.
[12 Jan 2006 4:18] Stuart Friedberg
I realize this has been classified as "not a bug", but I just got bitten by it.
Generally, I expect an unique key to be unique, and not conditionally on the
storage engine.

My workaround is to emulate nulls with an additional explicit flag column.
Oh joy.
[1 Mar 2006 14:16] Erland Lewin
I created bug #17825 as a Feature Request to add this functionality.
[26 May 2006 10:45] Tony Mountifield
The SQL-92 standard says:

"A unique constraint is satisfied if and only if no two rows in a table have the
same non-null values in the unique columns. In addition, if the unique
constraint was defined with PRIMARY KEY, then it requires that none of the
values in the specified column or columns be the null value."

That suggests that the current MySQL behaviour is wrong and needs to be fixed
(In MySQL 4 too).

So you should be able to have any number of rows where all the columns in a unique index are null, but only one row with a given combination of non-null columns.

create table tb (
  a int,
  b int,
  c int,
  unique index (a,b,c)
);

insert into tb(a,b,c) values (null,null,null); -- ok
insert into tb(a,b,c) values (null,null,null); -- still ok
insert into tb(a,b,c) values (null,null,null); -- still ok
insert into tb(a,b,c) values (1,null,null); -- ok
insert into tb(a,b,c) values (1,2,null); -- ok
insert into tb(a,b,c) values (1,2,3); -- ok
insert into tb(a,b,c) values (1,null,null); -- SHOULD FAIL, BUT DOESN'T
insert into tb(a,b,c) values (1,2,null); -- SHOULD FAIL, BUT DOESN'T
insert into tb(a,b,c) values (1,2,3); -- fails correctly
[22 Jun 2006 9:11] Sergei Golubchik
You read it wrong. "no two rows have the same non-null values in the unique columns" means that you can have many (1,NULL,NULL) rows, because they don't have "non-null values in the unique columns" at all.

The rule was slightly reformulated in SQL-2003 to make the above more explicit:

If there are no two rows in T such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row, then the result of the <unique predicate> is True; otherwise, the result of the <unique predicate> is False.

(it's for <unique predicate>, but never mind - <unique constraint> in sql-2003 is defined via unique predicate).
[17 Dec 2010 16:50] Markus Bäurle
Vote +1
[12 Apr 2011 17:40] Frederico Wuerges Becker
I have some problem with unique index and null values.
I'm using innoDB tables.
This problem have any fix?
[27 Jan 2012 0:24] Ronald Cole
Is a bug!  The ANSI SQL-92 standard decreed that two NULL values should be considered "not distinct".  The definition of not distinct in the ANSI standard includes any two values that return TRUE for an equality test, or any two NULLs.  This is why GROUP BY groups all nulls into a single partition.

<http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt> section 3.1.3(h) and section 7.7 general rule (1).

See also: <http://www.sqlservercentral.com/articles/Advanced+Querying/2829/> and <http://en.wikipedia.org/wiki/SQL#Null_and_three-valued_logic_.283VL.29>
[8 Aug 2012 18:38] John Ashmore
I changed all my null values to empty string, unfortunately.

Really not fun, I vote this be changed as well.
[9 Jan 2013 7:53] Vladimir Guts
Vote +1
[29 Jun 2013 11:44] Flavelle Ballem
Need to have this fixed. There are simply too many situations where a nullable column should be included as part of a unique index!
[27 Apr 2014 13:05] Leandro Carlos Rodrigues
A have the version 5.6 and this bug still present. For me, this is a bug. Anyone knows if Oracle will fix it?
[27 Apr 2014 13:15] Leandro Carlos Rodrigues
I have a solution for problem. The index type list has: INDEX, UNIQUE, FULLTEXT and PRIMARY. The solution is create one more type called UNIQUENULL or something like that. The DB administrator chooses de best type for his case.
[17 Oct 2014 14:59] Bao Richard
I also think this is a bug, wish one day oracle can fix this.
[21 Oct 2014 21:18] Alex P
This is definitely not a bug. 

NULL reflects a lack of data, an unknown. NULL != NULL because NULL could mean anything. If you have a unique combination of columns with a NULL in one of the columns it means that there is no data there, it's not equivalent to an empty string or a zero.

Example: Imagine you had a table of people that you want to be unique on first name, last name, and address with address being nullable. You could have as many "Bob, Smith, NULL"s as you want because their address is unknown. You wouldn't want a customer database that rejects potential customers as duplicate just because their information is incomplete.

If you don't want incomplete information to be accepted, make it non-nullable and put in an empty string.
[22 Oct 2014 15:50] Matthew Lenz
Since this isn't technically a bug (but it's one of the first results on the search engines when looking for this problem), here is a work around using the original example table.  If you attempt to add a "dupe" via insert or update you will get a unique key error on the new field. This solution is better (imo) than using default 0 because it still allows for the use of foreign key constraints.

1. Add a new field:

ALTER TABLE `tb`
ADD `unique_a_b` VARCHAR(45) NOT NULL DEFAULT '-';

2. Create a new unique index:

ALTER TABLE `tb`
ADD UNIQUE KEY `unique_a_b` (`unique_a_b`);

2. Create two triggers:

DELIMITER ;;
CREATE TRIGGER `tb_before_insert` BEFORE INSERT ON `tb`
FOR EACH ROW
BEGIN
    SET NEW.unique_a_b = CONCAT(IFNULL(NEW.a, ''), '-', IFNULL(NEW.b, ''));
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tb_Before_Update` BEFORE UPDATE ON `tb`
FOR EACH ROW
BEGIN
    IF NEW.a != OLD.a OR NEW.b != OLD.b THEN
        SET NEW.unique_a_b = CONCAT(IFNULL(NEW.a, ''), '-', IFNULL(NEW.b, ''));
    END IF;
END;;
DELIMITER ;
[7 Sep 2015 11:18] Daniel Fisher
Null is an unknown,  the value of NULL could be anything or nothing it is just unknown.

Null does not equal 0, and a NULL can never be equal to another NULL.   

I dont know a database that does not have this concept.   If this is a problem make the column mandatory and take null out of the unique index.
[21 Jul 2016 19:02] Maciej Pilichowski
"Null does not equal 0, and a NULL can never be equal to another NULL.   

I dont know a database that does not have this concept. ."

It depends on the context -- as for indices, if I am not mistaken MS-SQL server supports this (i.e. NULL behaves like a singular value).

But I hope everyone knows what NULL means, the key point is the behaviour for NULL being distinct (when it comes to index) has its uses *and* NULL not being distinct has its uses too.

Sure one can come up with workaround, but how many features are not fully supported to force users to use workaround after workaround. After a while you have database full of custom patches.

+1 vote to support BOTH scenarios, i.e. user would specify for given index, for given column how NULL should be treated.
[27 Feb 2018 17:40] jacmkno jacmkno
The idea that NULL represents an unknown is absurd in the context of optional relationships where NULL clearly represents the fact that there is no relationship (not an unknown relationship). Moreover, the current interpretation makes it imposible to add multi-field unique constraints to optional relationships.

So, if you need multi-field unique constraint that include an optional relationship, you need to make the relationship strict, and create a record representing "no relationship" in the destination table. It is just awful, and makes it necessary to handle this innocuous record in the application logic.

I would suggest adding a configuration variable to activate the "NULL_AS_VALUE" feature. There is precedence for this kind of changes in mysql where they made the full group by an optional feature:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Hope you can still consider this change.
[18 Oct 2018 7:53] Hubert Higelin
If NULL represent unknown
   NULL == NULL is false   AND   NULL != NULL is false

Unique mean (no other value == ) AND (all other values !=)
record1 NULL == record2 NULL → false → multiples NULL ok
BUT
record1 NULL != record2 NULL → false → MULTIPLES NULL NOT OK
[27 Jan 2020 9:30] Grzegorz Leszczyński
I also suggest to make 2 different unique indexes. Like Leandro Carlos Rodrigues wrote before: "The solution is create one more type called UNIQUENULL or something like that. The DB administrator chooses de best type for his case."

Please, implement it. It is not too much work, I think.