Does MySQL ignore null values on unique constraints?

user_434293 0
Viewed 143925

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?

unique-constraint mysql database
5 Answers

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'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 values

user_99858490
Related