Auto increment does not generally work on multi-column primary key in MYSQL

[Former user]

This ticket is related to #649.

The fix works when the explicit order of the autoincrement primary key is placed first.

1. This is not documented and took me a long time to figure out
2. There is a Mysql statement Key that can be attached to the autoincremet key to allow the primary key (not only the first in the list) to be auto incremented.

The following examples show the problem:

Python-Sqlalchemy: review = sa.Table('review', metadata, sa.Column('id', mysql.MSBigInteger, sa.ForeignKey('business.id'), primary_key=True, autoincrement=False), sa.Column('rid', sa.INT, primary_key=True), sa.Column('title', sa.TEXT), sa.Column('text', sa.TEXT), mysql_engine='InnoDB' )

Returns the Error:Incorrect table definition; there can be only one auto column and it must be defined as a key') u' CREATE TABLE review ( id BIGINT NOT NULL, rid INTEGER NOT NULL AUTO_INCREMENT, title TEXT, text TEXT, PRIMARY KEY (id, rid), FOREIGN KEY(id) REFERENCES business (id) )ENGINE=InnoDB

Swapping the first two fields around works, but there is no way of defining them the way displayed above.

Hatem Nassrat nassrat at cs d.o.t dal d.o.t ca

[Former user]

Sorrry should have used code braces.

Hatem

[Mike Bayer]

whats the solution here, that the emitted DDL should automatically change the order of columns so that the autoincrement is first ?

[jek]

For InnoDB, auto increment is only supported on the first column in a key. You can enable it on a secondary key, as you've discovered. In this case the incremented values will not re-start at 1 for each unique value in the primary key; instead they'll start at one and will be unique for every row. SA doesn't have a generic 'key' construct, but a unique key is functionally equivalent here:

   Column('id', INT, auto_increment=False, primary_key=True),
   Column('id2', INT, primary_key=True),
   UniqueConstraint('id2'),
   mysql_engine=InnoDB

This technique can also be used to "unlink" an auto increment column in MyISAM tables (and possibly other engines that support auto increment at any key position)- a secondary key containing the auto increment column first will trump a multi-column primary key.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)