Does MySQL ignore null values on unique constraints?

mysql database unique-constraint
Hugo Mota picture Hugo Mota · Sep 14, 2010 · Viewed 108.6k times · Source

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

Mark Byers picture Mark Byers · Sep 14, 2010

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.

Related questions

Unique (multiple columns) and null in one column

I have simple categories table. Category can have parent category (par_cat column) or null if it is main category and with the same parent category there shouldn't be 2 or more categories with the same name or url. Code for …

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More