Incorrect PrimaryKeyConstraint with composite key including auto-increment in v1.1.0b1

[Aaron Rosenfeld]

The following Python yields the expected table format:

from sqlalchemy import Column, create_engine, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import PrimaryKeyConstraint, UniqueConstraint

Base = declarative_base()

class Tbl(Base):
    __tablename__ = 'tbl'
    __table_args__ = (
        UniqueConstraint('a'),
        PrimaryKeyConstraint('b', 'a'),
    )

    a = Column(Integer)
    b = Column(Integer)

engine = create_engine(
    'mysql+pymysql://user:password@localhost/test'
    '?charset=utf8&use_unicode=0'
)
Base.metadata.create_all(engine)

> show create table tbl;

CREATE TABLE `tbl` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`b`,`a`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Adding an auto-increment flag to a:

from sqlalchemy import Column, create_engine, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import PrimaryKeyConstraint, UniqueConstraint

Base = declarative_base()

class Tbl(Base):
    __tablename__ = 'tbl'
    __table_args__ = (
        UniqueConstraint('a'),
        PrimaryKeyConstraint('b', 'a'),
    )

    a = Column(Integer, autoincrement=True)
    b = Column(Integer)

engine = create_engine(
    'mysql+pymysql://user:password@localhost/test'
    '?charset=utf8&use_unicode=0'
)
Base.metadata.create_all(engine)

Causes the primary key to be in the incorrect order:

CREATE TABLE `tbl` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In v1.0.13 there also seems to be odd behavior too:

CREATE TABLE `tbl` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`b`,`a`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[Mike Bayer]

this is by design. In the absence of your additional UNIQUE key, MySQL will reject AUTO INCREMENT if not on the first column of he constraint. the behavior is described at : http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#no-more-generation-of-an-implicit-key-for-composite-primary-key-w-auto-increment

Previously, SQLAlchemy forced this extra KEY to generate implicitly ( I think your "odd behavior in 1.0" example is missing this).

Can you fully describe your use case for a specific column ordering in a PRIMARY KEY with auto-increment on the second column?

[Aaron Rosenfeld]

I agree that the change is a good one but perhaps a warning or error that the primary key columns are being reorganized would be helpful? When we tested on 1.1, our foreign keys broke because the parent table no longer had the correctly ordered keys and figuring out the cause took some digging.

As far as out usecase, we have a number of processes each inserting rows into a table where each row is associated with a 'sample'. Within the sample each row is identified by the auto-increment value.

Later, we bulk-analyze the data in the context of each sample; that is, most of our queries select large amounts of data for a given sample ID. Thus the table was designed to physically organize (on-disk) the data based on sample rather than an auto-increment which may intersperse data from multiple samples across the table (because of the parallel insertions). The auto-increment still lets us do a quick select of a given record, however.

[Mike Bayer]

what I have in https://gerrit.sqlalchemy.org/126 will allow that your explicit PrimaryKeyConstraint maintains the ordering as given. only when the constraint is implicitly formed via primary_key=True flags will this behavior takes place. I think this will be a great way for things to work as expected for both use cases.

[Aaron Rosenfeld]

This looks like a great solution!

[Mike Bayer]

Don't reorder PrimaryKeyConstraint columns if explicit

Dialed back the "order the primary key columns per auto-increment" described in :ref:change_mysql_3216 a bit, so that if the :class:.PrimaryKeyConstraint is explicitly defined, the order of columns is maintained exactly, allowing control of this behavior when necessary.

Change-Id: I9e7902c57a96c15968a6abf53e319acf15680da0 Fixes: #3726

→ <<cset bf03d4332ae3>>