cascading delete-orphan relationships disrupted by joined table inheritance

This is difficult to explain, so let me just show you:

from sqlalchemy import String, Integer, Column, create_engine, ForeignKey
from sqlalchemy.orm import relationship, Session
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    employees = relationship('Employee', cascade='all,delete-orphan')


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String, nullable=False)
    name = Column(String, nullable=False)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)

    __mapper_args__ = {
        'polymorphic_on': 'type',
        'with_polymorphic': '*',
    }


class Programmer(Employee):
    __tablename__ = 'programmer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    languages = relationship('Language', cascade='all,delete-orphan')

    __mapper_args__ = {
        'polymorphic_identity': 'programmer',
    }


class Language(Base):
    __tablename__ = 'language'
    id = Column(Integer, primary_key=True)
    programmer_id = Column(
        Integer,
        ForeignKey('programmer.id'),
        nullable=False,
    )
    name = Column(String, nullable=False)


engine = create_engine('postgresql://localhost:5432/sa')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
db = Session(engine)


company = Company(
    id=1,
    name='Foobar Corp',
    employees=[Programmer(
        id=1,
        name='John Smith',
        languages=[Language(id=1, name='Python')],
    )],
)
db.add(company)
db.flush()
company.employees = []
db.flush()

What I expect to happen is the second flush would perform roughly the following SQL:

delete language where programmer_id=1
delete programmer where id = 1
delete employee where id = 1

Instead, I get the following error:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "programmer" violates foreign key constraint "language_programmer_id_fkey" on table "language" DETAIL: Key (id)=(1) is still referenced from table "language". [SQL: 'DELETE FROM programmer WHERE programmer.id = %(id)s'] [parameters: {'id': 1}]

I've played around with various configurations, and it seems to be a very specific case that fails:

Removing delete-orphan from Programmer.languages and making language.id nullable works fine.
Performing db.delete(programmer) instead of company.employees = [] works fine.

Comments (3)