Does MySQL ignore null values on unique constraints?

Question

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?

Answer 1

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.

Answer 2

From the docs:

"a UNIQUE index permits multiple NULL values for columns that can contain NULL"

This applies to all engines but BDB.