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
