8

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.

Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
  • Possibly a duplicate? http://stackoverflow.com/questions/1346765/unique-constraint-that-allows-empty-values-in-mysql – eaj Nov 15 '11 at 20:30
  • Yes, you are right. Thank you for posting a link to that thread. – Haradzieniec Nov 15 '11 at 20:38
  • 1
    I know many cases where unique and null, right now for example I'm doing this POS system and the email is not mandatory, but if the person adds an email it must be unique. – Dvid Silva Jun 22 '13 at 15:50

2 Answers2

22

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.

Thomas
  • 63,911
  • 12
  • 95
  • 141
6

Because NULL is not equal to NULL. Even though some RDMS, SQLServer for instance, treat 2 NULLs as equal when it comes to unique constraints.

a1ex07
  • 36,826
  • 12
  • 90
  • 103