Can Multiple NULL Values Coexist in a MySQL Column with a UNIQUE Constraint?

Unique Constraints and Null Values in MySQL

One may encounter scenarios where it's desirable to enforce uniqueness on a column while allowing null values. In MySQL, this behavior is supported in certain cases.

Let's consider a column named "email" that should adhere to the uniqueness rule but also accommodate null values. Can multiple null emails coexist in such a setup?

Yes, MySQL permits multiple NULLs in a column with a unique constraint. This can be demonstrated through a simple example:

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;

Output:

x
NULL
NULL
1

As illustrated, multiple null values can exist in a unique column in MySQL. This behavior is unique to MySQL and may not apply to other database management systems. For instance, SQL Server 2005 and earlier versions only allow a single NULL value in a column with a unique constraint.

The above is the detailed content of Can Multiple NULL Values Coexist in a MySQL Column with a UNIQUE Constraint?. For more information, please follow other related articles on the PHP Chinese website!