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
