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

User · Answer

From the docs       a UNIQUE index permits multiple NULL   values for columns that can contain   NULL    This applies to all engines but BDB

User · Answer

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  NULL and NULL is not allowed No two non-null rows can have values i e NULL and NULL is fine  but StackOverflow and StackOverflow is 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 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 values

User · Answer

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 UNIQUE NULL value    The question has already been answered  yes you can have duplicate NULL values while using the UNIQUE index   Since I stumbled upon this answer while searching for  how to allow one UNIQUE 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 UNIQUE 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

User · Answer

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

User · Answer

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

[mysql] Does MySQL ignore null values on unique constraints?

Examples related to mysql

Examples related to database

Examples related to unique-constraint