Description:
For InnoDB and MyISAM engines, would it be possible to make a PRIMARY KEY be able to have NULL columns, but then treat the NULL values as simply one other option, as is the case for BDB tables (or so I gather from the documentation, I do not have the BDB engine on my installation)?
How to repeat:
CREATE TABLE `test` (
`domain` VARCHAR( 50 ) NULL ,
`uri` VARCHAR( 250 ) NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `domain` , `uri` )
) TYPE = InnoDB; // table should be created properly (even with two NULL PRIMARY KEY columns)
INSERT INTO `test` VALUES ('example.com', NULL, 'welcome to example.com'),
(NULL, '/home', 'home page'),
(NULL, NULL, 'default index page'); // All of these columns should be added!
// this row however should NOT work after the above rows have been added
INSERT INTO `test` VALUES (NULL, '/home', 'home page 2'); // sorry, duplicate index for NULL-/home
// this one should work
INSERT INTO `test` VALUES (NULL, '/home2', 'home page 2'); // ok, this row is NULL-/home2, not NULL-/home
Suggested fix:
It would be nice to allow NULL to be another allowed value. I understand that the current PRIMARY KEY is the way it is because a UNIQUE index can have multiple NULL values per column.
Just another suggestion, I don't know if this flies in the face of database design or structure, but I think it could be useful....
Description: For InnoDB and MyISAM engines, would it be possible to make a PRIMARY KEY be able to have NULL columns, but then treat the NULL values as simply one other option, as is the case for BDB tables (or so I gather from the documentation, I do not have the BDB engine on my installation)? How to repeat: CREATE TABLE `test` ( `domain` VARCHAR( 50 ) NULL , `uri` VARCHAR( 250 ) NULL , `name` VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( `domain` , `uri` ) ) TYPE = InnoDB; // table should be created properly (even with two NULL PRIMARY KEY columns) INSERT INTO `test` VALUES ('example.com', NULL, 'welcome to example.com'), (NULL, '/home', 'home page'), (NULL, NULL, 'default index page'); // All of these columns should be added! // this row however should NOT work after the above rows have been added INSERT INTO `test` VALUES (NULL, '/home', 'home page 2'); // sorry, duplicate index for NULL-/home // this one should work INSERT INTO `test` VALUES (NULL, '/home2', 'home page 2'); // ok, this row is NULL-/home2, not NULL-/home Suggested fix: It would be nice to allow NULL to be another allowed value. I understand that the current PRIMARY KEY is the way it is because a UNIQUE index can have multiple NULL values per column. Just another suggestion, I don't know if this flies in the face of database design or structure, but I think it could be useful....