Can MySQL's Unique Constraint Handle Multiple NULL Values?

MySQL Unique Constraint: Null Values

In MySQL, unique constraints are enforced to guarantee the uniqueness of values in a specified column. However, when dealing with email addresses, a common scenario is the use of null values to represent an optional or unprovided address. This raises the question:

Can MySQL allow multiple null values in a column with a unique constraint?

Answer:

Yes, MySQL permits multiple null values within a column that has a unique constraint. This behavior deviates from certain other database systems.

Example:

The following SQL statement creates a table named table1 with an integer column x that is defined as unique and allows null values:

CREATE TABLE table1 (x INT NULL UNIQUE);

Inserting duplicate null values into this table will not raise an error:

INSERT table1 VALUES (1);
INSERT table1 VALUES (1);   -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);

Running a query to retrieve all records in table1 will produce the following result:

SELECT * FROM table1;

x

NULL

NULL

1

Note: This behavior is specific to MySQL. Other database systems, such as SQL Server 2005 and earlier, restrict the allowance of a single null value in columns with unique constraints.

The above is the detailed content of Can MySQL's Unique Constraint Handle Multiple NULL Values?. For more information, please follow other related articles on the PHP Chinese website!