How to cascade delete from parent using alembic?

Question

Maybe I'm missing something here that is super simple, but I have a postgresql db that has several tables structured as such:

- releases
 |- releases_contributions
 |- releases_primary contributions
 |- recording
   |- recording_contributions
   |- recording_primary_contributions

where releases_contributions, releases_primary_contributions & recording all have a column that has a ForeignKey associated with the auto generated releases_id.

I've made a revision to update the ondelete parameter:

    op.alter_column('recording', 'releases_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))
    op.alter_column('recording_contributions', 'recording_fk_id', sa.ForeignKey('recording.recording_id', ondelete='CASCADE'))
    op.alter_column('recording_primary_contributions', 'recording_fk_id', sa.ForeignKey('recording.recording_id', ondelete='CASCADE'))
    op.alter_column('releases_contributions', 'releases_fk_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))
    op.alter_column('releases_primary_contributions', 'releases_fk_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))

And in my initial alembic upgrade I have this, of course:

op.add_column('releases', 'releases_id', primary_key=True, autoincrement=True)

I'm trying to delete a row in releases

delete from releases where releases_id=1;

that then cascades down to the other tables and deletes any row that has that release as it's foreign key.

But whenever I run that command, I get the following error:

ERROR:  update or delete on table "releases" violates foreign key constraint "releases_contributions_releases_fk_id_fkey" on table "releases_contributions"
DETAIL:  Key (releases_id)=(1) is still referenced from table "releases_contributions".

What am I missing here?

I think you may need to do `op.drop_constraint` and `op.create_foreign_key` in your alembic revision in order to properly update the foreign key constraint with on delete cascade property. — Vivek, Nov 23 '21 at 02:03

score 2 · Accepted Answer · answered Nov 23 '21 at 14:02

Updating the alembic revision with the downgrades/upgrades below, then running alembic downgrade -1 and alembic upgrade head fixed this issue.

def upgrade():
    op.alter_column('recording', 'releases_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))
    op.alter_column('recording_contributions', 'recording_fk_id', sa.ForeignKey('recording.recording_id', ondelete='CASCADE'))
    op.alter_column('recording_primary_contributions', 'recording_fk_id', sa.ForeignKey('recording.recording_id', ondelete='CASCADE'))
    op.alter_column('releases_contributions', 'releases_fk_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))
    op.alter_column('releases_primary_contributions', 'releases_fk_id', sa.ForeignKey('releases.releases_id', ondelete='CASCADE'))


def downgrade():
    op.drop_contraint('releases_id', 'releases')
    op.drop_contraint('releases_id', 'recording')
    op.drop_contraint('releases_fk_id', 'releases_contributions')
    op.drop_contraint('releases_fk_id', 'releases_primary_contributions')
    op.drop_contraint('recording_fk_id', 'recording_contributions')
    op.drop_contraint('recording_fk_id', 'recording_primary_contributions')

How to cascade delete from parent using alembic?

1 Answers1