Bug #37130 Extra index is added to column when making SERIAL column PK.
Submitted: 1 Jun 2008 20:11
Reporter: Matthew Montgomery Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.44 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[1 Jun 2008 20:11] Matthew Montgomery
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)
[10 Aug 2010 21:17] Jack Ostroff
Isn't this the same issue as bug 48366?