List:General Discussion« Previous MessageNext Message »
From:John Larsen Date:August 29 2003 12:07pm
Subject:Re: Why are Duplicate keys allowed???
View as plain text  
This is perfectly legal and right. A null should not be used for a 
value, and null should be used where a value will be put but has not 
been yet, so if I plan on putting 3 entries in where they are all the 
same but that in future will be different this is correct. Anyways two 
NULLS should not be equal to eachother (I believe they may be in oracle, 
but someone else is welcome to correct me on that fact) when tested.

mos wrote:

> 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`)
> 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

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