Description:
When creating a column that uses the SERIAL type as the primary key, a useless UNIQUE index is created.
Currently : SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
How to repeat:
mysql> create table t2 (id serial primary key);
Query OK, 0 rows affected (0.09 sec)
mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t3 (id serial);
Query OK, 0 rows affected (0.23 sec)
mysql> show create table t3;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`id` bigint(20) unsigned NOT NULL auto_increment,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
/** Multi column PK **/
mysql> create table t4 (id serial, name varchar(40), primary key (id, name));
Query OK, 0 rows affected (0.22 sec)
mysql> show create table t4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`,`name`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
mysqld should not add this UNIQUE index when PK is specified for this column also.
I'm not sure how I would it should behave when SERIAL is used part of a multi-field PK. (probably current behavior is acceptable)
