MySQL: UNIQUE, but DEFAULT NULL - allowed by table creation. More than 1 NULL allowed to embed. What for?

Question

I just checked and allowed to create a table with a column that is NULL by default, although it is also a UNIQUE KEY:

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

As I understand it, it looks weird and doesn't make much sense. I expected the second insert

 INSERT INTO u789 VALUE (NULL); 

will fail.

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

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

Thanks.

Answer 1

According to the SQL 92 specification (and how you read it), unique constraints are intended to indicate candidate keys and, therefore, should not allow duplicate values ​​or NULL values. In this way, DB2 implements its unique limitations. More than a few database providers (including MySQL) read the specification as ignoring NULL values, just as the Group By clause ignores NULL values, and therefore they implement unique restrictions so that they only apply to non-NULL values. Still others treat NULL as their special value and allow only one entry, which is NULL. Therefore, Microsoft SQL Server implements unique constraints. The only aspect that is consistent among all providers regarding unique constraints is that non-NULL values ​​must be unique.

Answer 2

Because NULL not NULL . Despite the fact that some RDMS, SQLServer, for example, treats 2 NULL as equal when it comes to unique constraints.