MySQL: UNIQUE, but DEFAULT NULL - allowed by creating of table. More than 1 NULL is allowed to insert. Why?

Question

I've just checked and it's allowed to create a table with a column that is NULL by default, although it's a UNIQUE KEY at the same time:

CREATE TABLE IF NOT EXISTS `u789` (
`column1` varchar(10) DEFAULT NULL,
UNIQUE KEY (column1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

As I understand, it looks odd and has not much sense. I expected the second insert of

INSERT INTO u789 VALUE (NULL);

will fail.

But, it inserts first, second, third NULL value without any problems. Who can explain me why it iserts second and third columns if NULL is already in the table?

This is a theoretical question (as I understand nobody uses DEFAULT NULL + UNIQUE KEY for the same column in most situations), but I want to understand why it doesn't throw an error once one NULL is already in the column. Am I doing something wrong with declaring a unique column?

Thank you.

Answer 1

According to the SQL 92 specification (and how you read it) unique constraints are meant to denote candidate keys and therefore should not allow duplicate values nor NULL values. DB2 implements their unique constraints in this manner. More than a few database vendors (including MySQL) read the specification as ignoring NULL values much like the Group By clause ignores NULL values and thus they implement unique constraints such that it only applies to non-NULL values. Still others, treat the NULL as its own special value and only allow one entry which is NULL. Microsoft SQL Server implements unique constraints this way. The only aspect that is consistent amongst all vendors with respect to unique constraints is that non-NULL values must be unique.