List:General Discussion« Previous MessageNext Message »
From:mos Date:August 28 2003 8:10pm
Subject:Why are Duplicate keys allowed???
View as plain text  
I have a table with a UNIQUE compound index, Cust_Primary that is composed 
of 4 columns: Cust_Type, Area, Join_Date, Join_Time.
This index should allow for only unique entries, but it doesn't. If 
Join_Time is NULL then it allows for duplicates. Why?

CREATE TABLE `CustHistory2` (
   `Cust_Id` int(10) unsigned NOT NULL auto_Increment,
   `Area` char(2) default NULL,
   `Cust_Type` char(17) default NULL,
   `Join_Date` date default NULL,
   `Join_Time` time default null,
   PRIMARY KEY  (`Cust_Id`),
   UNIQUE KEY `Cust_Id` (`Cust_Id`),
   UNIQUE KEY `Cust_Primary` (`Cust_Type`,`Area`,`Join_Date`,`Join_Time`)
) TYPE=MyISAM


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.

The only way around this is to specify a NOT NULL for Join_Time and define 
a default value like 0 so it defaults to 12:00:00AM.
I don't see why I have to do this. Can someone shed some light on this? TIA

Mike

MySQL version 4.10 Alpha-Max-Nt


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