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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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;
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.
From the docs:
“a UNIQUE index permits multiple NULL
values for columns that can contain
This applies to all engines but BDB.
I am unsure if the author originally was just asking whether or not this allows duplicate values or if there was an implied question here asking, “How to allow duplicate
NULL values while using
UNIQUE?” Or “How to only allow one
The question has already been answered, yes you can have duplicate
NULL values while using the
Since I stumbled upon this answer while searching for “how to allow one
NULL value.” For anyone else who may stumble upon this question while doing the same, the rest of my answer is for you…
In MySQL you can not have one
NULL value, however you can have one
UNIQUE empty value by inserting with the value of an empty string.
Warning: Numeric and types other than string may default to 0 or another default value.
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.
A simple answer would be :
No, it doesn't
Explanation : According to the definition of unique constraints (SQL-92)
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
This statement can have two interpretations as :
- No two rows can have same values i.e.
NULLis not allowed
- No two non-null rows can have values i.e
NULLis fine, but
StackOverflowis not allowed
Since MySQL follows second interpretation, multiple
NULL values are allowed in
UNIQUE constraint column. Second, if you would try to understand the concept of
NULL in SQL, you will find that two
NULL values can’t be compared at all since
NULL in SQL refers to unavailable or unassigned value (you can’t compare nothing with nothing). Now, if you are not allowing multiple
NULL values in
UNIQUE constraint column, you are contracting the meaning of
NULL in SQL. I would summarise my answer by saying :
MySQL supports UNIQUE constraint but not on the cost of ignoring NULL