cascade delete not being used sqlalchemy

Question

I am getting the following error when I try to delete all entries from parent table

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "parent" violates foreign key constraint "child_p_id_fkey" on table "child" DETAIL: Key (id)=(1) is still referenced from table "child".

This is my code, and I have already tried adding cascadein either ends of relationship as per SQLAlchemy: cascade delete, in which case I get this instead

sqlalchemy.exc.ArgumentError: For many-to-one relationship Child.parent, delete-orphan cascade is normally configured only on the "one" side of a one-to-many relationship, and not on the "many" side of a many-to-one or many-to-many relationship. To force this relationship to allow a particular "Parent" object to be referred towards by only a single "Child" object at a time via the Child.parent relationship, which would allow delete-orphan cascade to take place in this direction, set the single_parent=True flag. (Background on this error at: https://sqlalche.me/e/14/bbf0)

from sqlalchemy import create_engine, Column, ForeignKey, Integer, delete
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        back_populates="parent",
        cascade="save-update, merge, delete, delete-orphan",
    )

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, back_populates="children")


engine = create_engine(
    "postgresql://user:pass@localhost:5432/postgres", future=True
)

Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    foo = Parent()
    session.add(foo)
    session.add(Child(parent=foo))

with Session(engine) as session:
    session.execute(delete(Parent).where(Parent.id == 1))

score 1 · Accepted Answer · answered Jan 09 '23 at 05:27

As I understand it when you use something like delete directly you step outside the orm. For the relationship cascades to execute you need to use session.delete(foo).

ORM DELETE

So in your last block you'd need to do something like this to trigger the cascades in your relationship on Parent:

with Session(engine) as session:
    # Select the parent back from the db
    foo = session.get(Parent, 1)
    # Now delete it with the ORM
    session.delete(foo)

BULK DELETE / CORE DELETE

To cascade when using session.execute(delete(Parent).where(Parent.id == 1)) you need to set ondelete='CASCADE' on the foreign key like this:

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id, ondelete='CASCADE'))
    parent = relationship(Parent, back_populates="children")

Combining relationship(..., cascade="...") and ondelete=... together is explained here:

using-foreign-key-on-delete-cascade-with-orm-relationships

I wanted a bulk delete and your solution works, just a quick follow up, do I need to setup some alembic migration if I add `ondelete='CASCADE'` to my column? or that only applies in python land and no migration is needed? — Valerie, Jan 09 '23 at 05:39
@Valerie Yes you will need to drop the existing foreign key and then recreate it. I don't think you need to change the column itself. Lookup the constraint name of the foreign key on the `Child` table, then use `op.drop_constraint` to drop that constraint and then use `op.create_foreign_key` to recreate it but this time set [`ondelete`](https://alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.create_foreign_key.params.ondelete). I think that should work. — Ian Wilson, Jan 09 '23 at 06:40

cascade delete not being used sqlalchemy

1 Answers1

ORM DELETE

BULK DELETE / CORE DELETE