Re: Why are Duplicate keys allowed???

John Larsen Fri, 29 Aug 2003 12:10:48 +0000

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why are Duplicate keys allowed???

Reply via email to