List:General Discussion« Previous MessageNext Message »
From:mos Date:August 28 2003 10:04pm
Subject:Re: Why are Duplicate keys allowed???
View as plain text  
At 03:30 PM 8/28/2003, you wrote:
>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

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


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