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

1 files changed, 82 insertions, 9 deletions

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)