- 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.

4 files changed, 240 insertions, 3 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index c957b2f8a..f6f2ec740 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2916,6 +2916,21 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE TABLE t (x INTEGER, z INTEGER)"
         )
 
+    def test_composite_pk_constraint_autoinc_first(self):
+        m = MetaData()
+        t = Table(
+            't', m,
+            Column('a', Integer, primary_key=True),
+            Column('b', Integer, primary_key=True, autoincrement=True)
+        )
+        self.assert_compile(
+            schema.CreateTable(t),
+            "CREATE TABLE t ("
+            "a INTEGER NOT NULL, "
+            "b INTEGER NOT NULL, "
+            "PRIMARY KEY (b, a))"
+        )
+
 
 class InlineDefaultTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = 'default'
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 673085cf7..8a0e27ebd 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -732,7 +732,6 @@ class AutoIncrementTest(fixtures.TablesTest):
         )
         assert x._autoincrement_column is None
 
-    @testing.fails_on('sqlite', 'FIXME: unknown')
     def test_non_autoincrement(self):
         # sqlite INT primary keys can be non-unique! (only for ints)
         nonai = Table(
@@ -746,8 +745,9 @@ class AutoIncrementTest(fixtures.TablesTest):
             # mysql in legacy mode fails on second row
             nonai.insert().execute(data='row 1')
             nonai.insert().execute(data='row 2')
-        assert_raises(
-            sa.exc.DBAPIError,
+        assert_raises_message(
+            sa.exc.CompileError,
+            ".*has no Python-side or server-side default.*",
             go
         )
 
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index f66f0b391..bdaf4f38c 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -390,6 +390,106 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             checkparams={"name_1": "foo"}
         )
 
+    def test_anticipate_no_pk_composite_pk(self):
+        t = Table(
+            't', MetaData(), Column('x', Integer, primary_key=True),
+            Column('y', Integer, primary_key=True)
+        )
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.y' is marked as a member.*"
+            "Note that as of SQLAlchemy 1.1,",
+            t.insert().compile, column_keys=['x']
+
+        )
+
+    def test_anticipate_no_pk_composite_pk_implicit_returning(self):
+        t = Table(
+            't', MetaData(), Column('x', Integer, primary_key=True),
+            Column('y', Integer, primary_key=True)
+        )
+        d = postgresql.dialect()
+        d.implicit_returning = True
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.y' is marked as a member.*"
+            "Note that as of SQLAlchemy 1.1,",
+            t.insert().compile, dialect=d, column_keys=['x']
+
+        )
+
+    def test_anticipate_no_pk_composite_pk_prefetch(self):
+        t = Table(
+            't', MetaData(), Column('x', Integer, primary_key=True),
+            Column('y', Integer, primary_key=True)
+        )
+        d = postgresql.dialect()
+        d.implicit_returning = False
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.y' is marked as a member.*"
+            "Note that as of SQLAlchemy 1.1,",
+            t.insert().compile, dialect=d, column_keys=['x']
+
+        )
+
+    def test_anticipate_nullable_composite_pk(self):
+        t = Table(
+            't', MetaData(), Column('x', Integer, primary_key=True),
+            Column('y', Integer, primary_key=True, nullable=True)
+        )
+        self.assert_compile(
+            t.insert(),
+            "INSERT INTO t (x) VALUES (:x)",
+            params={'x': 5},
+        )
+
+    def test_anticipate_no_pk_non_composite_pk(self):
+        t = Table(
+            't', MetaData(),
+            Column('x', Integer, primary_key=True, autoincrement=False),
+            Column('q', Integer)
+        )
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.x' is marked as a member.*"
+            "may not store NULL.$",
+            t.insert().compile, column_keys=['q']
+
+        )
+
+    def test_anticipate_no_pk_non_composite_pk_implicit_returning(self):
+        t = Table(
+            't', MetaData(),
+            Column('x', Integer, primary_key=True, autoincrement=False),
+            Column('q', Integer)
+        )
+        d = postgresql.dialect()
+        d.implicit_returning = True
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.x' is marked as a member.*"
+            "may not store NULL.$",
+            t.insert().compile, dialect=d, column_keys=['q']
+
+        )
+
+    def test_anticipate_no_pk_non_composite_pk_prefetch(self):
+        t = Table(
+            't', MetaData(),
+            Column('x', Integer, primary_key=True, autoincrement=False),
+            Column('q', Integer)
+        )
+        d = postgresql.dialect()
+        d.implicit_returning = False
+        assert_raises_message(
+            exc.CompileError,
+            "Column 't.x' is marked as a member.*"
+            "may not store NULL.$",
+            t.insert().compile, dialect=d, column_keys=['q']
+
+        )
+
 
 class InsertImplicitReturningTest(
         _InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 2e51b9a91..24f416439 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -1361,6 +1361,128 @@ class TableTest(fixtures.TestBase, AssertsCompiledSQL):
         assert not t1.c.x.nullable
 
 
+class PKAutoIncrementTest(fixtures.TestBase):
+    def test_multi_integer_no_autoinc(self):
+        pk = PrimaryKeyConstraint(
+            Column('a', Integer),
+            Column('b', Integer)
+        )
+        t = Table('t', MetaData())
+        t.append_constraint(pk)
+
+        is_(pk._autoincrement_column, None)
+
+    def test_multi_integer_multi_autoinc(self):
+        pk = PrimaryKeyConstraint(
+            Column('a', Integer, autoincrement=True),
+            Column('b', Integer, autoincrement=True)
+        )
+        t = Table('t', MetaData())
+        t.append_constraint(pk)
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "Only one Column may be marked",
+            lambda: pk._autoincrement_column
+        )
+
+    def test_single_integer_no_autoinc(self):
+        pk = PrimaryKeyConstraint(
+            Column('a', Integer),
+        )
+        t = Table('t', MetaData())
+        t.append_constraint(pk)
+
+        is_(pk._autoincrement_column, pk.columns['a'])
+
+    def test_single_string_no_autoinc(self):
+        pk = PrimaryKeyConstraint(
+            Column('a', String),
+        )
+        t = Table('t', MetaData())
+        t.append_constraint(pk)
+
+        is_(pk._autoincrement_column, None)
+
+    def test_single_string_illegal_autoinc(self):
+        t = Table('t', MetaData(), Column('a', String, autoincrement=True))
+        pk = PrimaryKeyConstraint(
+            t.c.a
+        )
+        t.append_constraint(pk)
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "Column type VARCHAR on column 't.a'",
+            lambda: pk._autoincrement_column
+        )
+
+    def test_single_integer_illegal_default(self):
+        t = Table(
+            't', MetaData(),
+            Column('a', Integer, autoincrement=True, default=lambda: 1))
+        pk = PrimaryKeyConstraint(
+            t.c.a
+        )
+        t.append_constraint(pk)
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "Column default.*on column t.a is not compatible",
+            lambda: pk._autoincrement_column
+        )
+
+    def test_single_integer_illegal_server_default(self):
+        t = Table(
+            't', MetaData(),
+            Column('a', Integer,
+                   autoincrement=True, server_default=func.magic()))
+        pk = PrimaryKeyConstraint(
+            t.c.a
+        )
+        t.append_constraint(pk)
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "Column server default.*on column t.a is not compatible",
+            lambda: pk._autoincrement_column
+        )
+
+    def test_implicit_autoinc_but_fks(self):
+        m = MetaData()
+        Table('t1', m, Column('id', Integer, primary_key=True))
+        t2 = Table(
+            't2', MetaData(),
+            Column('a', Integer, ForeignKey('t1.id')))
+        pk = PrimaryKeyConstraint(
+            t2.c.a
+        )
+        t2.append_constraint(pk)
+        is_(pk._autoincrement_column, None)
+
+    def test_explicit_autoinc_but_fks(self):
+        m = MetaData()
+        Table('t1', m, Column('id', Integer, primary_key=True))
+        t2 = Table(
+            't2', MetaData(),
+            Column('a', Integer, ForeignKey('t1.id'), autoincrement=True))
+        pk = PrimaryKeyConstraint(
+            t2.c.a
+        )
+        t2.append_constraint(pk)
+        is_(pk._autoincrement_column, t2.c.a)
+
+        t3 = Table(
+            't3', MetaData(),
+            Column('a', Integer,
+                   ForeignKey('t1.id'), autoincrement='ignore_fk'))
+        pk = PrimaryKeyConstraint(
+            t3.c.a
+        )
+        t3.append_constraint(pk)
+        is_(pk._autoincrement_column, t3.c.a)
+
+
 class SchemaTypeTest(fixtures.TestBase):
 
     class MyType(sqltypes.SchemaType, sqltypes.TypeEngine):