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

5 files changed, 49 insertions, 3 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index abb97229d..44ae8c2bb 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -34,6 +34,16 @@
         cases than we expected, which hints that perhaps there are some
         unknown string-INSERT cases too.
 
+    .. change::
+        :tags: bug, mysql
+        :tickets: 3726
+
+        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.
+
 .. changelog::
     :version: 1.1.0b1
     :released: June 16, 2016
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 9772213ef..4e54b97e1 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -2397,6 +2397,19 @@ of just stating the AUTO_INCREMENT column *first* within the primary key::
         PRIMARY KEY (y, x)
     )ENGINE=InnoDB
 
+To maintain explicit control of the ordering of primary key columns,
+use the :class:`.PrimaryKeyConstraint` construct explicitly (1.1.0b2)
+(along with a KEY for the autoincrement column as required by MySQL), e.g.::
+
+    t = Table(
+        'some_table', metadata,
+        Column('x', Integer, primary_key=True),
+        Column('y', Integer, primary_key=True, autoincrement=True),
+        PrimaryKeyConstraint('x', 'y'),
+        UniqueConstraint('y'),
+        mysql_engine='InnoDB'
+    )
+
 Along with the change :ref:`change_3216`, composite primary keys with
 or without auto increment are now easier to specify;
 :paramref:`.Column.autoincrement`
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index fc5073596..16ca7f959 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2519,7 +2519,9 @@ class DDLCompiler(Compiled):
                 text += "CONSTRAINT %s " % formatted_name
         text += "PRIMARY KEY "
         text += "(%s)" % ', '.join(self.preparer.quote(c.name)
-                                   for c in constraint.columns_autoinc_first)
+                                   for c in (constraint.columns_autoinc_first
+                                   if constraint._implicit_generated
+                                   else constraint.columns))
         text += self.define_constraint_deferrability(constraint)
         return text
 
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index cb01a49e3..ee139827a 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -475,7 +475,8 @@ class Table(DialectKWArgs, SchemaItem, TableClause):
         self.indexes = set()
         self.constraints = set()
         self._columns = ColumnCollection()
-        PrimaryKeyConstraint()._set_parent_with_dispatch(self)
+        PrimaryKeyConstraint(_implicit_generated=True).\
+            _set_parent_with_dispatch(self)
         self.foreign_keys = set()
         self._extra_dependencies = set()
         if self.schema is not None:
@@ -3023,6 +3024,10 @@ class PrimaryKeyConstraint(ColumnCollectionConstraint):
 
     __visit_name__ = 'primary_key_constraint'
 
+    def __init__(self, *columns, **kw):
+        self._implicit_generated = kw.pop('_implicit_generated', False)
+        super(PrimaryKeyConstraint, self).__init__(*columns, **kw)
+
     def _set_parent(self, table):
         super(PrimaryKeyConstraint, self)._set_parent(table)
 
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 8b4e5053b..27cab65ac 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -3066,7 +3066,7 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE TABLE t (x INTEGER, z INTEGER)"
         )
 
-    def test_composite_pk_constraint_autoinc_first(self):
+    def test_composite_pk_constraint_autoinc_first_implicit(self):
         m = MetaData()
         t = Table(
             't', m,
@@ -3081,6 +3081,22 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL):
             "PRIMARY KEY (b, a))"
         )
 
+    def test_composite_pk_constraint_maintains_order_explicit(self):
+        m = MetaData()
+        t = Table(
+            't', m,
+            Column('a', Integer),
+            Column('b', Integer, autoincrement=True),
+            schema.PrimaryKeyConstraint('a', 'b')
+        )
+        self.assert_compile(
+            schema.CreateTable(t),
+            "CREATE TABLE t ("
+            "a INTEGER NOT NULL, "
+            "b INTEGER NOT NULL, "
+            "PRIMARY KEY (a, b))"
+        )
+
     def test_create_table_suffix(self):
         class MyDialect(default.DefaultDialect):
             class MyCompiler(compiler.DDLCompiler):