SQLAlchemy FK ondelete does not RESTRICT

Question

I have a self referential relationship established. A person can have one a single parent (or None), and a person can have many children (or None).

So NULL is allowed as a FK:

class Person(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('person.id', ondelete='RESTRICT'))
    parent    = db.relationship('Person', remote_side=[id], back_populates='children')
    children  = db.relationship('Person', back_populates='parent')

However, I want to prohibit deletions of a Person if they are a parent. So I included the ondelete='RESTRICT' clause but it has no effect. The parent_id Column is still set to NULL when the parent is deleted.

(note my SQLite connection has swicthed pragma foreign key constraints to ON)

Why does the database not throw an Error when a parent is deleted and therefore a child Column with it as their foreign key restricts this?

SuperShoot · Answer 1 · 2019-05-03T12:17:17.253

Sqlalchemy is nulling the child rows before the database has a chance to evaluate the Foreign Key constraint. If you add passive_deletes=True to the relationship, sqlalchemy ~~won't try to manage the deletion of the related entities and just lets the database do it's thing depending on how you have it configured.~~ won't first issue a select to populate the relationship before deleting the parent. Setting to True will still result in child objects already in the session having their FK column set to NULL.

This configuration:

class Person(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('person.id', ondelete='RESTRICT'))
    parent    = db.relationship('Person', remote_side=[id], back_populates='children')
    children  = db.relationship('Person', back_populates='parent', passive_deletes=True)


if __name__ == '__main__':
    with app.app_context():
        db.drop_all()
        db.create_all()
        parent = Person()
        db.session.add(parent)
        child = Person(parent=parent)
        db.session.commit()
        db.session.delete(parent)
        db.session.commit()

Raises:

sqlalchemy.exc.IntegrityError: (mysql.connector.errors.IntegrityError) 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (test.person, CONSTRAINT person_ibfk_1 FOREIGN KEY (parent_id) REFERENCES person (id))

if __name__ == '__main__':
    with app.app_context():
        db.drop_all()
        db.create_all()
        parent = Person()
        db.session.add(parent)
        child = Person(parent=parent)
        db.session.commit()
        db.session.query(Person).all()  # reload the people into the session before deleting parent
        db.session.delete(parent)
        db.session.commit()

... will still null the parent_id field of the child, even with passive_deletes=True. So passive_deletes='all' is the way to go.

`True` is not always enough, though; cascades will still apply, if children have been loaded. `"all"` should be used in case of `RESTRICT`. `True` is good when the DB side cascade is, well, `CASCADE`, as well :D — Ilja Everilä, May 03 '19 at 11:52
...or `SET NULL` ([source](https://docs.sqlalchemy.org/en/13/orm/collections.html#passive-deletes)). — Ilja Everilä, May 03 '19 at 11:59
Thanks for that, I've only ever read the tutorial section of the docs regarding `passive_deletes` which doesn't mention that as an alternative (https://docs.sqlalchemy.org/en/13/orm/collections.html#passive-deletes). I'll have to start making a point of looking at the API docs more as that's a useful one to know! — SuperShoot, May 03 '19 at 12:01

Ilja Everilä · Accepted Answer · 2019-05-03T16:05:12.363

2

Your foreign key constraint setup looks correct, but your ORM relationships have no explicit cascade configuration, so they're using the defaults save-update and merge. In this default configuration the children relationship de-associates orphaned children when deleting the parent by setting their foreign key to NULL. I think you should use passive_deletes='all' (see the note on delete cascades) in this case to disable any ORM level cascades when deleting a parent, so that the database may prevent the delete when it is flushed:

class Person(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('person.id', ondelete='RESTRICT'))
    parent    = db.relationship('Person', remote_side=[id], back_populates='children')
    children  = db.relationship('Person', back_populates='parent', passive_deletes='all')

edited May 03 '19 at 16:05

answered May 03 '19 at 11:51

Ilja Everilä

50,538
7
126
127

So where a Foreign Key column is nullable, and you want `ondelete=RESTRICT` would you say this should be set to `'all'` as a rule? – SuperShoot May 03 '19 at 12:08
Thanks, for this, if I set the relationship to `viewonly=True` then it also works. Is there any inherent advantage to using your method over the `viewonly` approach? – Attack68 May 03 '19 at 12:10
1

From what I can tell, if you explicitly make changes to the relationship with the `passive_delete` approach, those changes will still flush. With the `viewonly` approach, even your explicit changes to the relationship collection won't flush. So I suppose it depends on how you intend on using the collection. – SuperShoot May 03 '19 at 13:59
1

@supershoot Don't know about rules, but on the other hand it'd seem a bit odd to have RESTRICT in the database, but entirely different behaviour in the ORM, i.e. de-associate or delete. From the docs: "Additionally, setting the flag to the string value ‘all’ will disable the “nulling out” of the child foreign keys, when the parent object is deleted and there is no delete or delete-orphan cascade enabled. This is typically used when a triggering or error raise scenario is in place on the database side." Having RESTRICT seems like an error raising scenario. – Ilja Everilä May 03 '19 at 16:09
1

I've not considered the interplay between the db `ondelete` actions and the ORM `cascades` to this extent, until now, so I'm grateful for your input and the OP's question here as it's clear to me now that when it's important, it's very important. Cheers. – SuperShoot May 03 '19 at 22:50

SQLAlchemy FK ondelete does not RESTRICT

2 Answers2