[Sqlalchemy-tickets] Issue #4272: MetaData.create_all() with a table that has a composite primary key and autoincrement (zzzeek/sqlalchemy)

[Rishi S. <iss...@bi...>]

New issue 4272: MetaData.create_all() with a table that has a composite primary key and autoincrement
https://bitbucket.org/zzzeek/sqlalchemy/issues/4272/metadatacreate_all-with-a-table-that-has-a

Rishi Sharma:

**Database**: MySQL 5.6

**SQLAlchemy**: 1.1

Using an example model definition as follows:
```
#!python

    class Page(base):
        __tablename__ = 'page'

        __table_args__ = (
            PrimaryKeyConstraint('book_id', 'page_id'),
        )

        book_id = Column(Integer())
        page_id = Column(Integer(), autoincrement=True)

        Index('idx_page_id', 'page_id', unique=True)
```

which emits the following SQL:


```
#!sql

CREATE TABLE page (
  book_id INTEGER NOT NULL,
  page_id INTEGER NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (book_id, page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci

```

which results in the following error from MySQL:


```
#!text

OperationalError: (_mysql_exceptions.OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')
```

because the emitted SQL does not include the index "idx_page_id". Looking at SQLAlchemy's source, indexes are created in a separate DDL after the CREATE.

I wanted to get your thoughts on this, and was curious if you know of a workaround when the primary key is composite with the second column being autoincrement.