Search code examples
mysqldatabaseunique-constraint

Does MySQL ignore null values on unique constraints?

I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?

Solution

  • Yes, MySQL allows multiple NULLs in a column with a unique constraint.

    CREATE TABLE table1 (x INT NULL UNIQUE);
INSERT table1 VALUES (1);
INSERT table1 VALUES (1);   -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);
SELECT * FROM table1;

    Result:

    x
NULL
NULL
1

    This is not true for all databases. SQL Server 2005 and older, for example, only allows a single NULL value in a column that has a unique constraint.