Have a look at this sql statement:
mysql> CREATE TABLE `t1` (`id` INT NULL AUTO_INCREMENT, KEY(`id`));
Query OK, 0 rows affected (0.92 sec)
mysql> SHOW CREATE TABLE `t1`;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)
See, how MySQL implicitly converts the NULLable column `id` to NOT NULLable because the column `id` was also a key?
Also have a look at the following statements:
mysql> CREATE TABLE `t2` (`id` INT NULL AUTO_INCREMENT KEY);
Query OK, 0 rows affected (0.11 sec)
mysql> SHOW CREATE TABLE `t2`;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Having KEY in the column specification makes that column a PRIMARY KEY!
I am going to go through these pages:
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
and try to understand why the context really matters while specifying the key. and also the reasoning behind implicitly changing stuff instead of throwing an error.ATM, it just doesn’t make any sense!