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]
