A single-column primary_key not picked up when it's also a ForeignKey

[candiduslynx]

Right as the title suggests:

1. table A, PK: col id: Mapped[UUID] = mapped_column(primary_key=True)
2. table B, col a_id: Mapped[UUID] = mapped_column(ForeignKey(A.id), primary_key=True)

this won't pick the PK for B

[zzzeek]

hi -

what do you mean by "picked up"? in the mapping? CREATE TABLE? Where / when?

[candiduslynx]

When generating alembic migration for the table it's missing the primary key.
And if I add the PK myself Alembic won't see it in the diff at all.

db: PostgreSQL 16

[zzzeek]

For generating the table itself, a result like that would be very surprising since such a basic issue would not have gone unnoticed. For the second part, detecting a PK change added to a table after the fact, alembic does not include those in autogenerate right now as there's a some complexity in creating PK migrations that just work without DB-specific caveats (more on the drop side than the create side).

For the first part,create a clean "alembic init" and add this to env.py:

from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase
from sqlalchemy import ForeignKey
import uuid

class Base(DeclarativeBase):
    pass

class A(Base):
    __tablename__ = "a"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True)

class B(Base):
    __tablename__ = "b"

    a_id: Mapped[uuid.UUID] = mapped_column(ForeignKey(A.id), primary_key=True)


target_metadata = Base.metadata

running autogenerate I get table defs that include pk and fk constraints, I cannot reproduce what you are describing:

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('a',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('b',
    sa.Column('a_id', sa.Uuid(), nullable=False),
    sa.ForeignKeyConstraint(['a_id'], ['a.id'], ),
    sa.PrimaryKeyConstraint('a_id')
    )
    # ### end Alembic commands ###

[candiduslynx]

I'll try to make the repro this week.
But noticed this when the migrations suddenly didn't add the PK to the table.

[zzzeek]

OK but note if you add primary_key=True after the CREATE TABLE already ran, autogenerate does not pick that part up, regardless of FK, you have to add an op.create_primary_key() manually.

[candiduslynx]

Ah, that might've been it!