does mysql ignore null values on unique constraints?

Question

i have an email column that i want it to be unique. but i also want it to accept null values. can my database have 2 null emails that way?

Answer 1

From the docs:

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

This applies to all engines but BDB.

Answer 2

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 for example only allows a single NULL value in a column that has a unique constraint.

Answer 3

Avoid nullable unique constraints. You can always put the column in a new table, make it non-null and unique and then populate that table only when you have a value for it. This ensures that any key dependency on the column can be correctly enforced and avoids any problems that could be caused by nulls.