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 [EMAIL PROTECTED]
Dan,
Thanks. That tends to make sense. I'll change the Join_Time to NOT NULL and add a default value to catch the duplicates.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
