List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 28 2003 8:30pm
Subject:Re: Why are Duplicate keys allowed???
View as plain text  
In the last episode (Aug 28), mos said:
> Example. Join_Time is usually NULL (but not always). I've discovered that 
> if Join_Time is NULL, MySQL allows for duplicate rows! !
> 
> Data:
> Cust_Type, Area, Join_Date, Join_Time
> 'BIG', 'N', '2003-01-01',NULL
> 'BIG', 'N', '2003-01-01',NULL
> 'BIG', 'N', '2003-01-01',NULL
> 
> These rows are allowed even though they are all duplicates. I could
> have the same row duplicated a thousand times. Why? Apparently MySQL
> only prevents duplicate rows if Join_Time is not NULL. It seems that
> if Join_Time is NULL then all bets are off and you can have as many
> duplicates as you like. I don't understand the reasoning behind this
> loophole.

NULL is a special value, and you are allowed to have more than one of
them them even in a UNIQUE index.  Since the test "NULL = NULL" returns
false, two otherwise identical records with a NULL in them are not
equal either and are not cinsidered duplicates.

See http://www.mysql.com/doc/en/Working_with_NULL.html and
http://www.mysql.com/doc/en/Problems_with_NULL.html for more info.
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
Why are Duplicate keys allowed???mos28 Aug
  • Re: Why are Duplicate keys allowed???Dan Nelson28 Aug
  • Re: Why are Duplicate keys allowed???John Larsen29 Aug
Re: Why are Duplicate keys allowed???mos29 Aug