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?

Answer 1

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')