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

3 files changed, 199 insertions, 42 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 691195772..f1220ce31 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2381,7 +2381,7 @@ class DDLCompiler(Compiled):
                 text += "CONSTRAINT %s " % formatted_name
         text += "PRIMARY KEY "
         text += "(%s)" % ', '.join(self.preparer.quote(c.name)
-                                   for c in constraint)
+                                   for c in constraint.columns_autoinc_first)
         text += self.define_constraint_deferrability(constraint)
         return text
 
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index e6f16b698..72b66c036 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -212,6 +212,7 @@ def _scan_cols(
 
     for c in cols:
         col_key = _getattr_col_key(c)
+
         if col_key in parameters and col_key not in check_columns:
 
             _append_param_parameter(
@@ -248,6 +249,10 @@ def _scan_cols(
             elif implicit_return_defaults and \
                     c in implicit_return_defaults:
                 compiler.returning.append(c)
+            elif c.primary_key and \
+                    c is not stmt.table._autoincrement_column and \
+                    not c.nullable:
+                _raise_pk_with_no_anticipated_value(c)
 
         elif compiler.isupdate:
             _append_param_update(
@@ -285,6 +290,22 @@ def _append_param_parameter(
 
 
 def _append_param_insert_pk_returning(compiler, stmt, c, values, kw):
+    """Create a primary key expression in the INSERT statement and
+    possibly a RETURNING clause for it.
+
+    If the column has a Python-side default, we will create a bound
+    parameter for it and "pre-execute" the Python function.  If
+    the column has a SQL expression default, or is a sequence,
+    we will add it directly into the INSERT statement and add a
+    RETURNING element to get the new value.  If the column has a
+    server side default or is marked as the "autoincrement" column,
+    we will add a RETRUNING element to get at the value.
+
+    If all the above tests fail, that indicates a primary key column with no
+    noted default generation capabilities that has no parameter passed;
+    raise an exception.
+
+    """
     if c.default is not None:
         if c.default.is_sequence:
             if compiler.dialect.supports_sequences and \
@@ -303,9 +324,12 @@ def _append_param_insert_pk_returning(compiler, stmt, c, values, kw):
             values.append(
                 (c, _create_prefetch_bind_param(compiler, c))
             )
-
-    else:
+    elif c is stmt.table._autoincrement_column or c.server_default is not None:
         compiler.returning.append(c)
+    elif not c.nullable:
+        # no .default, no .server_default, not autoincrement, we have
+        # no indication this primary key column will have any value
+        _raise_pk_with_no_anticipated_value(c)
 
 
 def _create_prefetch_bind_param(compiler, c, process=True, name=None):
@@ -342,18 +366,46 @@ def _process_multiparam_default_bind(compiler, c, index, kw):
 
 
 def _append_param_insert_pk(compiler, stmt, c, values, kw):
+    """Create a bound parameter in the INSERT statement to receive a
+    'prefetched' default value.
+
+    The 'prefetched' value indicates that we are to invoke a Python-side
+    default function or expliclt SQL expression before the INSERT statement
+    proceeds, so that we have a primary key value available.
+
+    if the column has no noted default generation capabilities, it has
+    no value passed in either; raise an exception.
+
+    """
     if (
-            (c.default is not None and
-             (not c.default.is_sequence or
-                 compiler.dialect.supports_sequences)) or
-            c is stmt.table._autoincrement_column and
-            (compiler.dialect.supports_sequences or
-             compiler.dialect.
-             preexecute_autoincrement_sequences)
+            (
+                # column has a Python-side default
+                c.default is not None and
+                (
+                    # and it won't be a Sequence
+                    not c.default.is_sequence or
+                    compiler.dialect.supports_sequences
+                )
+            )
+            or
+            (
+                # column is the "autoincrement column"
+                c is stmt.table._autoincrement_column and
+                (
+                    # and it's either a "sequence" or a
+                    # pre-executable "autoincrement" sequence
+                    compiler.dialect.supports_sequences or
+                    compiler.dialect.preexecute_autoincrement_sequences
+                )
+            )
     ):
         values.append(
             (c, _create_prefetch_bind_param(compiler, c))
         )
+    elif c.default is None and c.server_default is None and not c.nullable:
+        # no .default, no .server_default, not autoincrement, we have
+        # no indication this primary key column will have any value
+        _raise_pk_with_no_anticipated_value(c)
 
 
 def _append_param_insert_hasdefault(
@@ -555,3 +607,24 @@ def _get_returning_modifiers(compiler, stmt):
 
     return need_pks, implicit_returning, \
         implicit_return_defaults, postfetch_lastrowid
+
+
+def _raise_pk_with_no_anticipated_value(c):
+    msg = (
+        "Column '%s.%s' is marked as a member of the "
+        "primary key for table '%s', "
+        "but has no Python-side or server-side default generator indicated, "
+        "nor does it indicate 'autoincrement=True' or 'nullable=True', "
+        "and no explicit value is passed.  "
+        "Primary key columns typically may not store NULL."
+        %
+        (c.table.fullname, c.name, c.table.fullname))
+    if len(c.table.primary_key.columns) > 1:
+        msg += (
+            " Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be "
+            "indicated explicitly for composite (e.g. multicolumn) primary "
+            "keys if AUTO_INCREMENT/SERIAL/IDENTITY "
+            "behavior is expected for one of the columns in the primary key. "
+            "CREATE TABLE statements are impacted by this change as well on "
+            "most backends.")
+    raise exc.CompileError(msg)
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 137208584..210c6338c 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -572,18 +572,9 @@ class Table(DialectKWArgs, SchemaItem, TableClause):
     def _init_collections(self):
         pass
 
-    @util.memoized_property
+    @property
     def _autoincrement_column(self):
-        for col in self.primary_key:
-            if (col.autoincrement and col.type._type_affinity is not None and
-                    issubclass(col.type._type_affinity,
-                               type_api.INTEGERTYPE._type_affinity) and
-                    (not col.foreign_keys or
-                     col.autoincrement == 'ignore_fk') and
-                    isinstance(col.default, (type(None), Sequence)) and
-                    (col.server_default is None or
-                     col.server_default.reflected)):
-                return col
+        return self.primary_key._autoincrement_column
 
     @property
     def key(self):
@@ -913,17 +904,31 @@ class Column(SchemaItem, ColumnClause):
           argument is available such as ``server_default``, ``default``
           and ``unique``.
 
-        :param autoincrement: This flag may be set to ``False`` to
-          indicate an integer primary key column that should not be
-          considered to be the "autoincrement" column, that is
-          the integer primary key column which generates values
-          implicitly upon INSERT and whose value is usually returned
-          via the DBAPI cursor.lastrowid attribute.   It defaults
-          to ``True`` to satisfy the common use case of a table
-          with a single integer primary key column.  If the table
-          has a composite primary key consisting of more than one
-          integer column, set this flag to True only on the
-          column that should be considered "autoincrement".
+        :param autoincrement: Set up "auto increment" semantics for an integer
+          primary key column.  The default value is the string ``"auto"``
+          which indicates that a single-column primary key that is of
+          an INTEGER type should receive auto increment semantics automatically;
+          all other varieties of primary key columns will not.  This
+          includes that :term:`DDL` such as Postgresql SERIAL or MySQL
+          AUTO_INCREMENT will be emitted for this column during a table
+          create, as well as that the column is assumed to generate new
+          integer primary key values when an INSERT statement invokes which
+          will be retrieved by the dialect.
+
+          The flag may be set to ``True`` to indicate that a column which
+          is part of a composite (e.g. multi-column) primary key should
+          have autoincrement semantics, though note that only one column
+          within a primary key may have this setting.   It can also be
+          set to ``False`` on a single-column primary key that has a
+          datatype of INTEGER in order to disable auto increment semantics
+          for that column.
+
+          .. versionchanged:: 1.1 The autoincrement flag now defaults to
+             ``"auto"`` which indicates autoincrement semantics by default
+             for single-column integer primary keys only; for composite
+             (multi-column) primary keys, autoincrement is never implicitly
+             enabled; as always, ``autoincrement=True`` will allow for
+             at most one of those columns to be an "autoincrement" column.
 
           The setting *only* has an effect for columns which are:
 
@@ -940,8 +945,8 @@ class Column(SchemaItem, ColumnClause):
                             primary_key=True, autoincrement='ignore_fk')
 
             It is typically not desirable to have "autoincrement" enabled
-            on such a column as its value intends to mirror that of a
-            primary key column elsewhere.
+            on a column that refers to another via foreign key, as such a column
+            is required to refer to a value that originates from elsewhere.
 
           * have no server side or client side defaults (with the exception
             of Postgresql SERIAL).
@@ -969,12 +974,6 @@ class Column(SchemaItem, ColumnClause):
             to generate primary key identifiers (i.e. Firebird, Postgresql,
             Oracle).
 
-          .. versionchanged:: 0.7.4
-              ``autoincrement`` accepts a special value ``'ignore_fk'``
-              to indicate that autoincrementing status regardless of foreign
-              key references.  This applies to certain composite foreign key
-              setups, such as the one demonstrated in the ORM documentation
-              at :ref:`post_update`.
 
         :param default: A scalar, Python callable, or
             :class:`.ColumnElement` expression representing the
@@ -1128,7 +1127,7 @@ class Column(SchemaItem, ColumnClause):
         self.system = kwargs.pop('system', False)
         self.doc = kwargs.pop('doc', None)
         self.onupdate = kwargs.pop('onupdate', None)
-        self.autoincrement = kwargs.pop('autoincrement', True)
+        self.autoincrement = kwargs.pop('autoincrement', "auto")
         self.constraints = set()
         self.foreign_keys = set()
 
@@ -1263,12 +1262,12 @@ class Column(SchemaItem, ColumnClause):
 
         if self.primary_key:
             table.primary_key._replace(self)
-            Table._autoincrement_column._reset(table)
         elif self.key in table.primary_key:
             raise exc.ArgumentError(
                 "Trying to redefine primary-key column '%s' as a "
                 "non-primary-key column on table '%s'" % (
                     self.key, table.fullname))
+
         self.table = table
 
         if self.index:
@@ -3025,11 +3024,96 @@ class PrimaryKeyConstraint(ColumnCollectionConstraint):
 
         self.columns.extend(columns)
 
+        PrimaryKeyConstraint._autoincrement_column._reset(self)
         self._set_parent_with_dispatch(self.table)
 
     def _replace(self, col):
+        PrimaryKeyConstraint._autoincrement_column._reset(self)
         self.columns.replace(col)
 
+    @property
+    def columns_autoinc_first(self):
+        autoinc = self._autoincrement_column
+
+        if autoinc is not None:
+            return [autoinc] + [c for c in self.columns if c is not autoinc]
+        else:
+            return list(self.columns)
+
+    @util.memoized_property
+    def _autoincrement_column(self):
+
+        def _validate_autoinc(col, raise_):
+            if col.type._type_affinity is None or not issubclass(
+                col.type._type_affinity,
+                    type_api.INTEGERTYPE._type_affinity):
+                if raise_:
+                    raise exc.ArgumentError(
+                        "Column type %s on column '%s' is not "
+                        "compatible with autoincrement=True" % (
+                            col.type,
+                            col
+                        ))
+                else:
+                    return False
+            elif not isinstance(col.default, (type(None), Sequence)):
+                if raise_:
+                    raise exc.ArgumentError(
+                        "Column default %s on column %s.%s is not "
+                        "compatible with autoincrement=True" % (
+                            col.default,
+                            col.table.fullname, col.name
+                        )
+                    )
+                else:
+                    return False
+            elif (
+                col.server_default is not None and
+                    not col.server_default.reflected):
+                if raise_:
+                    raise exc.ArgumentError(
+                        "Column server default %s on column %s.%s is not "
+                        "compatible with autoincrement=True" % (
+                            col.server_default,
+                            col.table.fullname, col.name
+                        )
+                    )
+                else:
+                    return False
+            elif (
+                    col.foreign_keys and col.autoincrement
+                    not in (True, 'ignore_fk')):
+                return False
+            return True
+
+        if len(self.columns) == 1:
+            col = list(self.columns)[0]
+
+            if col.autoincrement is True:
+                _validate_autoinc(col, True)
+                return col
+            elif (
+                col.autoincrement in ('auto', 'ignore_fk') and
+                    _validate_autoinc(col, False)
+            ):
+                return col
+
+        else:
+            autoinc = None
+            for col in self.columns:
+                if col.autoincrement is True:
+                    _validate_autoinc(col, True)
+                    if autoinc is not None:
+                        raise exc.ArgumentError(
+                            "Only one Column may be marked "
+                            "autoincrement=True, found both %s and %s." %
+                            (col.name, autoinc.name)
+                        )
+                    else:
+                        autoinc = col
+
+            return autoinc
+
 
 class UniqueConstraint(ColumnCollectionConstraint):
     """A table-level UNIQUE constraint.