[Sqlalchemy-tickets] Issue #3986: cascading delete-orphan relationships disrupted by joined table inheritance (zzzeek/sqlalchemy)

[Theron L. <iss...@bi...>]

New issue 3986: cascading delete-orphan relationships disrupted by joined table inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issues/3986/cascading-delete-orphan-relationships

Theron Luhn:

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

```
#!python

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.