SQLAlchemy: `relationship` interferes with `cascade='DELETE'`

sqlalchemy cascading delete oddity.md

There's an oddity in SQLAlchemy foreign key support when you try to get deletes to cascade.

Here's a typical parent/child relationship. As coded, deleting a parent cascades to its children. (Since we're using SQLite3, we need to intervene to enable foreign key support, but that's an extraneous detail here. The Flask bits are due to this being extracted from a larger app.)

As coded you can't ask a parent for its children. For that, you need to enable one of (1) or (2). Form (1) appears in a lot of examples, but using it will result in an IntegrityViolation complaining that parent_id can't be set to NULL. Uh... WTF?

The reason is kind of hinted at in https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship

If you omit the cascade parameter, it takes defaults which do the wrong thing in this case, which is to effectively turn CASCADE into SET NULL.

You need to enable (2) instead.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import event
from sqlalchemy.engine import Engine

class Config:
    SQLALCHEMY_DATABASE_URI = 'sqlite:///repro.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # (1) # children = db.relationship("Child")  
    # (2) # children = db.relationship("Child", cascade='all,delete-orphan')  

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer,
        db.ForeignKey('parent.id', ondelete='CASCADE'),
        nullable=False)

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON;")
    cursor.close()

app_context = app.app_context()
app_context.push()
db.create_all()

parent = Parent()
db.session.add(parent)
db.session.commit()

child = Child()
child.parent_id = parent.id
db.session.add(child)
db.session.commit()

print("parent: {} child: {}".format(
  len(Parent.query.all()),
  len(Child.query.all())))
# should print "parent: 1 child: 1" on first run

db.session.delete(parent)
db.session.commit()  # KABOOM here if you use (1)

print("parent: {} child: {}".format(
  len(Parent.query.all()),
  len(Child.query.all())))
# should print "parent: 0 child: 0" on first run