415

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?

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Hugo Mota
  • 11,200
  • 9
  • 42
  • 60

6 Answers6

611

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.

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 68
    excellent comment about how it's true in mysql, but not necessarily in general. – user2910265 Sep 13 '14 at 01:52
  • 16
    According to [SQLite FAQ](https://sqlite.org/faq.html#q26), behavior _**is**_ same in MySQL, PostgreSQL, SQLite, Oracle, and Firebird. – Amir Ali Akbari Nov 07 '16 at 08:55
  • 5
    Please update your answer. SQLServer 2008+ absolutely allows for it, you simply have to add a WHERE clause... in 2017, nobody should be on an older version than 2008 anyway... https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls/767702#767702 – Mathieu Turcotte Jul 19 '17 at 12:33
  • this little feature has been very difficult to find an answer to that does not require adding a new column to the database or upgrading MySQL on a very old application. I was really looking for a solution like Postgres where I can use COALESCE but it seems the answer is always it not a bug it how it is designed. Not even `WHERE column IS NOT NULL` seems to fail me as it not supported in my version of MySQL. Anyone know where I could look? – User128848244 Nov 20 '18 at 17:13
  • 5
    note: this also works also unique indexes that have more columns. So if you want columns a, b and c to be unique you can still have in the table double rows with null, b, c – Mihai Crăiță Oct 13 '19 at 08:39
142

From the docs:

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

This applies to all engines but BDB.

Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
19

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.

Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
bluegman991
  • 707
  • 4
  • 9
  • 1
    Constraint has nothing to do with the index. In fact, you won't even be able to have a single row with NULL value despite the fact there is no other such row. – Pijusn Jan 15 '17 at 14:45
  • 1
    @Pijusn What do you mean by "constraint has nothing to do with index?" Also about your second sentence, I never said that you could have a row with a NULL value, that's why I stated at the beginning of the post, that this is a solution only if he isn't set on using null values. – bluegman991 Jan 15 '17 at 17:28
  • What I meant is that adding new element fails not because of `UNIQUE` constraint but because of `NOT NULL` constraint. I think this answer is irrelevant to the question because the question is specifically about the behaviour of `UNIQUE` constraint. – Pijusn Jan 15 '17 at 19:49
  • 1
    @Pijusn I got you. You're right, I have removed the wording suggesting otherwise. I mis-read the question. But I believe the answer still may be useful for users that stumble upon this question as I did while trying to find a way to have a unique "nothing" value, but are mistakenly allowing null-ability. – bluegman991 Jan 15 '17 at 21:30
  • I found this answer useful; I need to uniquely identify users on 3 parameters, but depending on the value of the second parameter, the third parameter is optional. I'll need to do Rails validation instead. – Devon Parsons Mar 16 '17 at 18:12
  • 1
    I found this answer useful. However, it is also answered [here](https://stackoverflow.com/questions/4081783/unique-key-with-nulls). This post was the first result from my google search, though this answer and the linked question were what I was looking for. – kingledion Sep 08 '17 at 13:46
  • 1
    Also you can have a generated column with `IFNULL(field_name, '')` and create an index on that. – toraman May 21 '21 at 04:44
  • Using an empty string, or zero is no use if your column is a foreign key to some other table, but the suggestion from @toraman was just what I needed. – Daniel Howard Jun 08 '23 at 18:08
10

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 9
    Yes, but what you propose is almost exactly what mysql does behind the scenes already. Why re-invent the wheel if this functionality is built in? – ProfileTwist Mar 12 '14 at 06:37
  • 4
    Because it's not valid SQL. I believe that this tip will be useful for all who want (or need) a database agnostic design. – Arsen7 Dec 09 '14 at 09:03
  • @Arsen7 What if you have multiple businesses - each with multiple clients. You store all business with their clients' email addresses in one file. So you can not make email_address unique because different businesses may have the same client. So you have to make a composite unique index of business_id and email_address. Is it possible to put this in a new table - as explained? – Gerhard Liebenberg Feb 02 '15 at 15:53
  • @GerhardLiebenberg, yes of course that is possible. – nvogel Feb 02 '15 at 16:52
  • 4
    I have a case where an "email" column needs to be unique OR null. I'd have to create a new table with a single "email" column if I were to follow your advice. Relying on this Mysql specific behavior is much easier and the result is the same. The customer doesn't care whether I store the email in a new table or not. Also, database agnostic design is all to often overrated. For a lot of projects, you can't and probably wouldn't just switch from one DB to another that easily. – conradkleinespel Jun 18 '19 at 08:34
  • @conradkleinespel I also dont see any point for an extra table. At MS SQL server you can define custom unique constraints with condition `WHERE column IS NOT NULL`. In some tables you even have multiple nullable constraints. Looks very overhead to have a table for each constraint. – djmj May 31 '20 at 19:22
  • @djml, Keys (i.e. irreducible sets of unique, non nullable columns) are simply what is implied by BCNF, namely "every determinant becomes a superkey". The universal relation assumption suggests that any key *could* be "hidden" simply by joining two tables together and potentially making some attributes nullable. The whole idea of normalization is to avoid that situation because keys and key dependencies are so important. – nvogel Jun 01 '20 at 15:23
  • Because BCNF implies it does not makes it a practical solution. The asker already allows null in its application and with this decision i gave a solution. MySQL provides a built in proofed solution. Alternatively you do not need to use a database unique constraint as i mentioned you can apply a check constraint or check it in the application to guarantee uniqueness. – djmj Jun 01 '20 at 17:47
  • 1
    @djmj sure, but functional dependencies are important to most people and the nullable unique constraint version does not enforce the same dependencies as the BCNF version. So which option is more or less practical may depend on which dependencies are important to you. That's why it is worth considering creating a new table. – nvogel Jun 02 '20 at 05:03
8

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

Akshay Katiha
  • 404
  • 5
  • 9
  • 1
    There is only one interpretation possible... 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 – Sebi2020 Jun 17 '22 at 14:18
  • Yes, I agree with @Sebi2020. There is only one and only one interpretation of the statement because it clearly says "same non-null values". Actually, NULL in the database is not even a value. Null is a representation of non-existence in the Database world and that is why it permits it. Uniqueness does not apply to something that does not exist. – AbdulelahAGR Oct 05 '22 at 20:22
0

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

YanAlex
  • 99
  • 1
  • 10