SQLAlchemy: deleting in multitable polymorphism

Question

I have model hierarchy like this: ``` class Foo(Base): id = Column( Integer, Sequence('foo_id_seq', start=1001, increment=1), primary_key=True ) discriminator = Column('type', String(20), nullable=False) __tablename__ = 'foo' __mapper_args__ = { 'polymorphic_on': discriminator, 'polymorphic_identity': 'foo', } class Bar(Foo): id = Column(Integer, ForeignKey('foo.id'), primary_key=True) __tablename__ = 'bar' __mapper_args__ = { 'polymorphic_identity': 'bar', } ``` And when I try to delete all `Foo` instances with `db.query(Foo).delete()` I get ``` IntegrityError: (IntegrityError) ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" DETAIL: Key (id)=(68575) is still referenced from table "bar". ``` Well, the error makes sense, but how to make it work? I need something like `cascade` in relationships, but for polymorhpism. I could not find it anywhere. All I came up with was creating a relationship, only to have `cascade` in there, but t…

Accepted Answer

There's two general ways to delete objects in this case. One is the traditional ORM way:

```
session.delete(some_bar)
```

When this session is flushed, it will emit two distinct DELETE statements, one for the "bar" table and one for the "foo" table. However, the ORM per-object delete() only works on one primary key at a time.

The other way, which I think is what you're trying to do, is something like this:

```
session.query(Foo).filter(...).delete()
```

with that kind of delete, we're emitting DELETE across a criteria. Some of the "foo" rows might have a "bar" row pointing to them, others not. In relational databases we can have the DB take care of this for us by setting up ON DELETE CASCADE. SQLAlchemy lets you configure this on ForeignKey as:

```
ForeignKey('foo.id', ondelete="CASCADE")
```

The above FK has to take place in a table create operation; such as `metadata.create_all()` where you'll see output like:

```
CREATE TABLE bar (
    id SERIAL NOT NULL, 
    foo_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(foo_id) REFERENCES foo (id) ON DELETE CASCADE
)
```

Then, when you delete rows from "foo", even at the Postgresql command line, the matching rows in "bar" will be deleted automatically. SQLAlchemy doesn't get in the way of this so it will happen when you use something like query.delete() as well.

SQLAlchemy: deleting in multitable polymorphism

Solution

Problem