Bug #43188 primary key on data type serial column occurs double index

• Submitted: 25 Feb 2009 14:04
• Modified: 25 Feb 2009 14:07
• Reporter: Susanne Ebrecht
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.1
• OS: Linux (windows)
• CPU Architecture: Any

[25 Feb 2009 14:04] Susanne Ebrecht

Description:
CREATE TABLE t(i serial, primary key(i));
SHOW CREATE TABLE t;

CREATE TABLE `t` (
  `i` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`),
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The system should recognise that there is already a unique key and should replace it against the primary key.

How to repeat:
See above

Suggested fix:
...

[16 Mar 2009 13:31] Sergei Golubchik

MySQL doesn't recognize when an index is redundant or useless (like INDEX (a,a,a)). Either situation is not an error, strictly speaking.
Still it'll probably be more user-friendly to catch these.
Valid feature request, by all means.