Bug #15109 Compound PKs with auto_increment don't work in InnoDB
Submitted: 21 Nov 2005 20:24 Modified: 13 May 2010 16:04
Reporter: Kristian Koehntopp Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.15 OS:Linux (Debian stable)
Assigned to: Sunny Bains CPU Architecture:Any

[21 Nov 2005 20:24] Kristian Koehntopp
I know that compound PKs with an auto_increment value are an abomination, still they are there out in the field. When converting some 600+ tables at a customer site from MyISAM to InnoDB, I had about 20+ tables that could not be converted.

This is because InnoDB does not support compound primary keys where one column is auto_increment.

How to repeat:
create table one ( txt varchar(20), ai not null auto_increment, primary key(txt, ai)) engine=myisam;

This one works.

create table two (txt varchar(20), ai not null auto_increment primary key(txt, ai)) engine=innodb;

This one is an ERROR 1075(42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.

That should be consistent across table handlers, it is arguable which engine is correct here.

Suggested fix:
Make it one way or the other, but don't leave it as it is.
[21 Nov 2005 20:30] Kristian Koehntopp
mysql> create table three ( txt varchar(20), ai integer not null auto_increment, primary key (txt, ai )) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Yes, I did get the syntax right, it really is an inconsistency between MyISAM and InnoDB. I am not complaining about the behaviour of InnoDB (or MyISAM) here. I am complaining that this is inconsistent, and this inconsistency is a problem in migration scenarios. Make it consistent, please.
[21 Nov 2005 20:33] Kristian Koehntopp
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Dean points me to http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html. This is a documented limitation. 1/2 :)
[22 Nov 2005 11:55] Heikki Tuuri
This is a feature that has been requested many times for InnoDB. Changing the 'Severity' to 'Feature request'.
[28 Nov 2005 13:47] Jan Lindström
Because so many have requested this feature I will try to implement it. Heikki, should this be implemented to 5.0 or 5.1 ? This should not be a big change. Actually, I think this can be implemented changing table flags, at the moment I don't see any reason why any other parts of the code needs a change.

[10 Jun 2006 12:35] Andre Timmer
In this regard same behaviour for innodb tables as MyISAM would be nice.
[21 Dec 2007 12:57] Stuardo Rodríguez
almost starting year 2008  (today is 2007-12-21) version 5.0.45 here and this is a "Feature Request"

I agree, this is not as fundamental as others, but has been in the queue for too long. It would be very apreciated if someone could asign time to do this.  Thanks for the greate work
[31 Oct 2008 10:09] Bernd Niedergesaess
Any news on this one?
It is in here for very long but still not resolved (even if stated, that it shouldn't be hard to resolve).
So when can we expect a target version date for this one?
[6 Oct 2010 4:43] henri pelkonen

Any news about this?