CODEWITHSUNDEEP
mysqldatabaseunique-constraint
Hugo Mota
Hugo Mota

Reputation: 11557

Does MySQL ignore null values on unique constraints?

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?

Upvotes: 437

Views: 183968

Answers (7)

user433342
user433342

Reputation: 1036

CREATE TABLE table1 (x INT NULL,
    _unq_x char GENERATED ALWAYS as (coalesce(x,'')) unique);
INSERT table1(x) VALUES (1);
INSERT table1(x) VALUES (1);   -- Duplicate entry '1' for key 'table1._unq_x'
INSERT table1(x) VALUES (NULL);
INSERT table1(x) VALUES (NULL);-- Duplicate entry '1' for key 'table1._unq_x'
SELECT x FROM table1;

Result:

x
1
NULL

Upvotes: 0

YanAlex
YanAlex

Reputation: 119

As a recommendation, make a virtual column if(column is null,1,NULL), then add it to index instead of the nullable field.

Upvotes: 2

bluegman991
bluegman991

Reputation: 727

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 cannot 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.

Upvotes: 21

Akshay Katiha
Akshay Katiha

Reputation: 470

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

Upvotes: 7

Mark Byers
Mark Byers

Reputation: 838216

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.

Upvotes: 641

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 10

Matthew Flaschen
Matthew Flaschen

Reputation: 284816

From the docs:

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

This applies to all engines but BDB.

Upvotes: 148

Related Questions