Bug #19377 Semantic of UNIQUE-Index on NULL values definable on DDL-Level
Submitted: 26 Apr 2006 16:20 Modified: 22 May 2006 10:46
Reporter: Jochen Riehm Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:n.a. OS:Any (all)
Assigned to: CPU Architecture:Any

[26 Apr 2006 16:20] Jochen Riehm 
Description:
As far as I understand it, a UNIQUE index on a NULLable field allows to NULLs to be inserted as long as the table engine is not BDB. It would be helpful if this semantic could be defined in the DDL on a per field basis, independent of the storage engine. 

Then, the myisam_stats_method server variable could also be replaced by that definition or a mechanism like feature request 19327 could be used.

How to repeat:
CREATE TABLE uni (
	a INT NOT NULL,
	b INT NULL,
	UNIQUE INDEX (a,b)
);
INSERT INTO uni VALUES (1, NULL);
INSERT INTO uni VALUES (1, NULL);
[22 May 2006 10:46] Valeriy Kravchuk 
Thank you for the feature request. I believe, treating NULLs as different in UNIQUE key columns is a part of SQL 2003 Standard. Other databases (Oracle e.g.) also works this way. So, I am not sure your feature will be ever implemented.