Foreign Key becomes None when using uppercase naming convention

[david-fed]

Hi,

I noticed some strange behavior when trying to migrate Foreign Keys on SQLite. Specifically when the naming_convention for foreign key is upper case (refer to the examples below).

Specs:

• Windows 11
• Python 3.14.0
• alembic 1.17.1
• SQLite

Sample model:

from sqlalchemy import ForeignKeyConstraint, MetaData
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    metadata = MetaData(
        naming_convention={
            "ix": "IX_%(column_0_label)s",
            "uq": "UQ_%(table_name)s_%(column_0_name)s",
            "ck": "CK_%(table_name)s_%(constraint_name)s",
            "fk": "FK_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", # this seems to be the culprit
            "pk": "PK_%(table_name)s",
        },
    )


class Parent(Base):
    __tablename__ = "parent"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    favorite_child_id: Mapped[int] = mapped_column()

    __table_args__ = (ForeignKeyConstraint(["favorite_child_id"], ["child.id"]),)


class Child(Base):
    __tablename__ = "child"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    parent_id: Mapped[int] = mapped_column()

    __table_args__ = (ForeignKeyConstraint(["parent_id"], ["parent.id"]),)

Steps to reproduce:

1. alembic revision -m "test1" --autogenerate
2. alembic upgrade head
3. Remove the foreign keys from the model
4. alembic revision -m "test2" --autogenerate

Revision 1:

...
def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('child',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('parent_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['parent_id'], ['parent.id'], name=op.f('FK_child_parent_id_parent')),
    sa.PrimaryKeyConstraint('id', name=op.f('PK_child'))
    )
    op.create_table('parent',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('favorite_child_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['favorite_child_id'], ['child.id'], name=op.f('FK_parent_favorite_child_id_child')),
    sa.PrimaryKeyConstraint('id', name=op.f('PK_parent'))
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('parent')
    op.drop_table('child')
    # ### end Alembic commands ###

Revision 2:

...
def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('child', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')

    with op.batch_alter_table('parent', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('parent', schema=None) as batch_op:
        batch_op.create_foreign_key(None, 'child', ['favorite_child_id'], ['id'])

    with op.batch_alter_table('child', schema=None) as batch_op:
        batch_op.create_foreign_key(None, 'parent', ['parent_id'], ['id'])

    # ### end Alembic commands ###
...

But if I change the naming_convention for the foreign key to lower case (FK_... -> fk_...) and repeat the steps, the 2nd revision works fine:

...
def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('child', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_child_parent_id_parent'), type_='foreignkey')

    with op.batch_alter_table('parent', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_parent_favorite_child_id_child'), type_='foreignkey')

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('parent', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_parent_favorite_child_id_child'), 'child', ['favorite_child_id'], ['id'])

    with op.batch_alter_table('child', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_child_parent_id_parent'), 'parent', ['parent_id'], ['id'])

    # ### end Alembic commands ###

I also tried to reproduce this behavior with primary keys and unique keys but these worked fine. Seems to only impact foreign keys.

[zzzeek]

the SQLite dialect is not reflecting case-sensitive names of FK, PK and unique constraints because the regex does not accommodate for quoting. so this is SQLAlchemy issue sqlalchemy/sqlalchemy#12954

[zzzeek]

well FK names. UQ and PK still investigating

[zzzeek]

that fix is merged for sqlalhcemy 2.0.45 next release

[david-fed]

Great, thanks for the quick fix!