How to nullify childre's foreign key when parent deleted using sqlalchemy?

Question

I have basic Flask application with Parent and Child models like that:

class Parent(db.Model):
    __tablename__ = 'parents'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)


class Child(db.Model):
    __tablename__ = 'children'

    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parents.id'), nullable=False)
    parent = db.relationship('Parent', backref=db.backref('children', cascade='all,delete'))
    name = db.Column(db.String)

As database I am using Postgres if it is important. Now I want to do following: remove cascade='all,delete' from child and make this parent_id nullable. I.e. when Parent removed from DB Child stays in place with parent_id == NULL.

I know that I could specify it with schema creation script adding constraint to FK. But I want just to mark it as NULL and allow SqlAlchemy take control on nullification of children's FK.

Answer 1

It's explained in the relevant section of the documentation in a great detail. Make sure you also read "ORM-level “delete” cascade vs. FOREIGN KEY level “ON DELETE” cascade" section to understand differences between proposed solutions.

ORM level

Now I want to do following: remove cascade='all,delete' from child and make this parent_id nullable.

Do it and you will get exact behaviour you want.

class Child(db.Model):
    __tablename__ = 'children'

    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parents.id'), nullable=True)
    parent = db.relationship('Parent', backref=db.backref('children'))
    name = db.Column(db.String)

Also note, that all is the synonym for save-update, merge, refresh-expire, expunge, delete, so all, delete is the same as simply all.

DB level

If you want to have ON DELETE SET NULL constraint on the database level, you can specify ondelete='SET NULL' in the ForeighKey definition or do nothing (since it's default behaviour for the foreign key). To get it working on the DB level you also need to set passive_deletes to either True or 'all' (see docs for the difference).

class Child(db.Model):
    __tablename__ = 'children'

    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parents.id', ondelete='SET NULL'), nullable=True)
    parent = db.relationship('Parent', backref=db.backref('children', passive_deletes=True))
    name = db.Column(db.String)