- The system by which a :class:`.Column` considers itself to be an

"auto increment" column has been changed, such that autoincrement
is no longer implicitly enabled for a :class:`.Table` that has a
composite primary key.  In order to accommodate being able to enable
autoincrement for a composite PK member column while at the same time
maintaining SQLAlchemy's long standing behavior of enabling
implicit autoincrement for a single integer primary key, a third
state has been added to the :paramref:`.Column.autoincrement` parameter
``"auto"``, which is now the default. fixes #3216
- The MySQL dialect no longer generates an extra "KEY" directive when
generating CREATE TABLE DDL for a table using InnoDB with a
composite primary key with AUTO_INCREMENT on a column that isn't the
first column;  to overcome InnoDB's limitation here, the PRIMARY KEY
constraint is now generated with the AUTO_INCREMENT column placed
first in the list of columns.

2 files changed, 23 insertions, 19 deletions

diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 304c31012..60af82bab 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -511,9 +511,8 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(schema.CreateTable(t1),
                             'CREATE TABLE sometable (assigned_id '
                             'INTEGER NOT NULL, id INTEGER NOT NULL '
-                            'AUTO_INCREMENT, PRIMARY KEY (assigned_id, '
-                            'id), KEY idx_autoinc_id (id))ENGINE=Inn'
-                            'oDB')
+                            'AUTO_INCREMENT, PRIMARY KEY (id, assigned_id)'
+                            ')ENGINE=InnoDB')
 
         t1 = Table('sometable', MetaData(),
                    Column('assigned_id', Integer(), primary_key=True,
@@ -537,8 +536,7 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             'CREATE TABLE sometable ('
             'id INTEGER NOT NULL, '
             '`order` INTEGER NOT NULL AUTO_INCREMENT, '
-            'PRIMARY KEY (id, `order`), '
-            'KEY idx_autoinc_order (`order`)'
+            'PRIMARY KEY (`order`, id)'
             ')ENGINE=InnoDB')
 
     def test_create_table_with_partition(self):
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index 39b39e006..266fc335e 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -211,49 +211,55 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         meta = MetaData(testing.db)
         try:
             Table('ai_1', meta,
-                  Column('int_y', Integer, primary_key=True),
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True),
-                         mysql_engine='MyISAM')
+                  mysql_engine='MyISAM')
             Table('ai_2', meta,
-                  Column('int_y', Integer, primary_key=True),
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True),
-                         mysql_engine='MyISAM')
+                  mysql_engine='MyISAM')
             Table('ai_3', meta,
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
-                  Column('int_y', Integer, primary_key=True),
-                         mysql_engine='MyISAM')
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
+                  mysql_engine='MyISAM')
             Table('ai_4', meta,
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
                   Column('int_n2', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
-                         mysql_engine='MyISAM')
+                  mysql_engine='MyISAM')
             Table('ai_5', meta,
-                  Column('int_y', Integer, primary_key=True),
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
                   Column('int_n', Integer, DefaultClause('0'),
                          primary_key=True, autoincrement=False),
-                         mysql_engine='MyISAM')
+                  mysql_engine='MyISAM')
             Table('ai_6', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
-                  Column('int_y', Integer, primary_key=True),
-                         mysql_engine='MyISAM')
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
+                  mysql_engine='MyISAM')
             Table('ai_7', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
                   Column('o2', String(1), DefaultClause('x'),
                          primary_key=True),
-                  Column('int_y', Integer, primary_key=True),
-                         mysql_engine='MyISAM')
+                  Column('int_y', Integer, primary_key=True,
+                         autoincrement=True),
+                  mysql_engine='MyISAM')
             Table('ai_8', meta,
                   Column('o1', String(1), DefaultClause('x'),
                          primary_key=True),
                   Column('o2', String(1), DefaultClause('x'),
                          primary_key=True),
-                         mysql_engine='MyISAM')
+                  mysql_engine='MyISAM')
             meta.create_all()
 
             table_names = ['ai_1', 'ai_2', 'ai_3', 'ai_4',