Automatic migrations wants to drop and recreate Foreign Keys created with create_foreign_key

[asyte]

After creating a foreignkey using op.create_foreign_key(), a new auto-migration wanted to delete and recreate a the foreign keys. these keys had the same name, and were in the correct schemas. I had no luck working through a number of similar solved issues (mostly involving schemas), however I was able to solve this issue by rebuilding the foreignkeys using sa.ForeignKeyConstraint() instead.

I'm using:

• alembic 1.11.1
• SQLAlchemy 2.0.14
• python 3.11.1
• postgres 14.6

[zzzeek]

hi -

there's any number of reasons this could happen, check that the names of the FKs were actually generated in the database correctly.

I know that's kind of sparse advice however there's no details here for me to go on.

[asyte]

Hello, Unfortunately I've moved past it. I'll try to replicate it and do some digging. From what I saw in the database the names of the fkeys were the same, but I most likely missed something.

[zzzeek]

oh, no worries at all, if you've resolved on your end we're most likely done. FK autogenerate on PostgreSQL is a very mature feature in Alembic, so any significant issues would be reported here quite a lot.

[asyte]

I was able to replicate it using this code
from project/models/player.py

from sqlalchemy.orm import DeclarativeBase

class ModelBase(DeclarativeBase): ...

class Inventory(ModelBase):
    __tablename__ = 'inventories'
    id: Mapped[int] = mapped_column(sa.Integer(), nullable=False, primary_key=True)

class User(ModelBase):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(sa.Integer(), nullable=False, primary_key=True)

class Player(ModelBase):
    __tablename__ = 'players'
    __table_args__ = (
        sa.Index('idx_players_name', 'name'),
    )
    user_id: Mapped[int] = mapped_column(
        sa.ForeignKey(User.id, name='fk_player_user_id', ondelete='CASCADE'),
        nullable=False, 
        primary_key=True,
    )
    name: Mapped[str] = mapped_column(sa.String(32), nullable=False)
    inventory_id: Mapped[int] = mapped_column(
        sa.ForeignKey(Inventory.id, name='fk_player_inventory_id', ondelete='CASCADE'),
        nullable=False
    )

from the old migration

# from old migration
def upgrade() -> None:
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False, primary_key=True),
    )

    op.create_table('players',
        sa.Column('user_id', sa.Integer(), nullable=False, primary_key=True),
        sa.Column('name', sa.String(length=32), nullable=False),
        sa.Column('inventory_id', sa.Integer(), nullable=False),

        #sa.ForeignKeyConstraint(['inventory_id'], ['inventories.id'], name='fk_player_inventory_id', ondelete='CASCADE'),
        #sa.ForeignKeyConstraint(['user_id'], ['users.id'], name='fk_player_user_id', ondelete='CASCADE'),
        #sa.PrimaryKeyConstraint('user_id')
    )
    op.create_index('idx_players_name', 'players', ['name'])
    op.create_foreign_key('fk_player_user_id', 'players', 'users', ['user_id'], ['id'])
    op.create_foreign_key('fk_player_inventory_id', 'players', 'inventories', ['inventory_id'], ['id'])

in psql

psql> \d players
                                               Table "public.players"
       Column        |            Type             | Collation | Nullable |                 Default                  
---------------------+-----------------------------+-----------+----------+------------------------------------------
 user_id             | integer                     |           | not null | nextval('players_user_id_seq'::regclass)
 name                | character varying(32)       |           | not null | 
 inventory_id        | integer                     |           | not null |
Indexes:
    "players_pkey" PRIMARY KEY, btree (user_id)
    "idx_players_name" btree (name
Foreign-key constraints:
    "fk_player_inventory_id" FOREIGN KEY (inventory_id) REFERENCES inventories(id)
    "fk_player_user_id" FOREIGN KEY (user_id) REFERENCES users(id)

from the new auto-migration

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_player_user_id', 'players', type_='foreignkey')
    op.drop_constraint('fk_player_inventory_id', 'players', type_='foreignkey')
    op.create_foreign_key('fk_player_user_id', 'players', 'users', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_player_inventory_id', 'players', 'inventories', ['inventory_id'], ['id'], ondelete='CASCADE')
    # ### end Alembic commands ###

It's also easily fixed by using the commented out fkey definitions instead of the op.create_foreign_key() definitions

[zzzeek]

your old migration is lacking the "ON DELETE CASCADE" options in your op.create_foreign_key(). That's why it's correctly trying to drop and recreate them. notice the cascades being added.

[asyte]

that was it! Thanks for the help.

[thomas-brandstaetter]

How can it possibly be a "correct" behaviour, when the same fk is drop and recreated in the same auto-migration? When the fk points from table_a.column_a to table_b.column_a, why should it be dropped? There is no valid reason for this behaviour.

Im struggle with the same issue

[thomas-brandstaetter]

How can the drop and recreation of identical fkeys possibly a valid behaviour? There is no reason for that. When a fkey point from table_a to table_b on certain columns, which is the case in the described issue, then these fkey are identical, and should not be touched by alembic.

[zzzeek]

the fkeys are not identical in the case given here. they are different. there is no ALTER for foreign keys, they have to be dropped and recreated when they change.

[thomas-brandstaetter]

your old migration is lacking the "ON DELETE CASCADE" options in your op.create_foreign_key(). That's why it's correctly trying to drop and recreate them. notice the cascades being added.

How can the drop and recreation of identical fkeys possibly a valid behaviour? There is no reason for that. When a fkey point from table_a to table_b on certain columns and have the same name, which is the case in the described issue, then these fkey are identical, and should not be touched by alembic auto-migration.

[zzzeek]

the ON UPDATE CASCADE phrase was added. That's not identical.

[CaselIT]

if you have a different case please create a new discussion with an example of what's not working.