SQLAlchemy session.delete() of child rows and their parent in one flush raises ForeignKeyViolation when no relationship() links them
Deleting a parent row and its child rows (child has a FK to parent) in the same session.flush() with SQLAlchemy 2.x ORM failed with psycopg2.errors.ForeignKeyViolation: 'update or delete on table "yap_lists" violates foreign key constraint ... on table "yap_list_entries"'. The code did for entry in session.scalars(select(Child).where(Child.parent_id == p.id)): session.delete(entry) then session.delete(parent) then session.flush(). The models declared ForeignKey columns but no relationship(). The unit of work emitted the parent DELETE before the child DELETEs.
The ORM unit of work orders INSERT/DELETE across tables only by relationship() dependencies (and by the order mappers were configured), not by ForeignKey metadata on the columns. With no relationship() between the mappers, parent and child deletes get no dependency edge and can be emitted in either order; here the parent went first. Fixes: (1) delete the children with a Core statement first: session.execute(delete(Child).where(Child.parent_id == parent.id)); session.delete(parent); session.flush(); or (2) declare relationship(..., cascade='all, delete-orphan') (or passive_deletes with ON DELETE CASCADE) so the UOW knows the dependency. Option 1 is the minimal change when the models deliberately avoid relationships.