Alembic - Using composite primary keys causes incorrect table definition in MySQL

Question

I have multiple "versioned" database SQLAlchemy models that use composite primary keys, achieved by combining an auto-incrementing integer field ("id") and a datetime field ("record_valid_from"). I'm trying to run this model in a local Docker container against a MySQL database.

The model definition is roughly as follows:

from sqlalchemy.orm import (DeclarativeBase, Mapped)

class classA(DeclarativeBase):
    id: Mapped[int] = mapped_column(primary_key=True, index=True, autoincrement=True)
    record_valid_from: Mapped[datetime] = mapped_column(DateTime,
        primary_key=True,
        default=get_current_timestamp # this is a python method returning datetime.now()
    )
    active: Mapped[bool] = mapped_column(Boolean, default=True,
        comment="TRUE if latest version, FALSE otherwise"
    )
    ... # some more fields and logic

Other models look similar, with various relationships between them.

When using Alembic to automatically generate migration scripts (alembic revision --autogenerate -m "init database"), the generated Python code appears to produce invalid SQL statements.

More specifically, I encountered:

(pymysql.err.OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')< ;/pre>
<p>Here is the migration code (note: I have simplified it): </p>
<pre class="brush:php;toolbar:false;">def upgrade() -> None:
    op.create_table('classA',
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('record_valid_from', sa.DateTime(), nullable=False),
        sa.Column('active', sa.Boolean(), nullable=False),
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.PrimaryKeyConstraint('record_valid_from', 'id')
    )
    op.create_index(op.f('ix_classA_id'), 'classA', ['id'], unique=False)

Has anyone experienced a similar situation? Or know how to solve this problem?

I tried the following:

• Call op.create_primary_key after creating the table (see: https://alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.create_primary_key). Result: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1068, 'Multiple primary keys defined').
• Remove sa.PrimaryKeyConstraint and call op.create_primary_key directly. result:
  • The migration was successful and running normally.
  • Attempting to create a new ORM model resulted in the following error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1364, "Field 'id' has no default value").

Answer 1

I spent a few hours solving this problem and fixed it myself. For anyone who encounters a similar problem, here is the answer:

Actually, the order of the primary key fields in the PrimaryKeyConstraint has an impact. My problem was solved by changing the order, instead of using sa.PrimaryKeyConstraint('record_valid_from', 'id'), I changed to sa.PrimaryKeyConstraint("id", "record_valid_from").

Hope this helps.