Prevent deletion of parent row if it's child will be orphaned in SQLAlchemy

Question

I'm currently building a data model using sqlalchemy through flask-sqlalchemy

The database is on a Postgresql server

I am having trouble when deleting rows from a table that has relationships. In this case I have a number of treatment types, and one treatment. the treatment has a single treatment type assigned.

As long as I have one or more treatments assigned a particular treatment Type, I wish that the treatment type cannot be deleted. As it is now it is deleted when I try.

I have the following model:

class treatment(db.Model):
    __tablename__ = 'treatment'
    __table_args__ = (db.UniqueConstraint('title', 'tenant_uuid'),)

    id = db.Column(db.Integer, primary_key=True)
    uuid = db.Column(db.String(), nullable=False, unique=True)
    title = db.Column(db.String(), nullable=False)
    tenant_uuid = db.Column(db.String(), nullable=False)

    treatmentType_id = db.Column(db.Integer, db.ForeignKey('treatmentType.id'))
    riskResponse_id = db.Column(db.Integer, db.ForeignKey('riskResponse.id'))

class treatmentType(db.Model):
    __tablename__ = 'treatmentType'
    __table_args__ = (db.UniqueConstraint('title', 'tenant_uuid'),)

    id = db.Column(db.Integer, primary_key=True)
    uuid = db.Column(db.String(), nullable=False, unique=True)
    title = db.Column(db.String(), nullable=False)
    tenant_uuid = db.Column(db.String(), nullable=False)

    treatments = db.relationship('treatment', backref='treatmentType', lazy='dynamic')

I can build some logic in my "delete" view that checks for assigned treatments, before deleting the treatment type, but in my opinion this should be a standard feature of a relational database. So in other words I must be doing something wrong.

I delete the treatment type like so:

entry = treatmentType.query.filter_by(tenant_uuid=session['tenant_uuid']).all()
    try:
        db.session.delete(entry)
        db.session.commit()
        return {'success': 'Treatment Type deleted'}
    except Exception as E:
        return {'error': unicode(E)}

As I said it is possible for me to do a check before deleting the treatment Type, but I would rather have sqlalchemy throw an error if there are relationship issues prior to deletion.

Answer 1

When deleting the TreatmentType (parent), by default, SQLAlchemy will update the child by setting the Treatment.treatmentType_id = None. As you stated, you are left with a Treatment without a TreatmentType. The child record is now an "orphan".

There are 2 ways to prevent orphaned records from being created in SQLAlchemy.

1. Using a Non-NULL constraint on the child column

When deleting the TreatmentType (parent), by default, SQLAlchemy will set the Treatment.treatmentType_id (child) to None (or null in SQL) when you perform this operation, and as you stated, you are left with a Treatment without a TreatmentType.

A solution to this is to update the treatmentType_id column to be non-nullable, meaning that it MUST have a non-null value. We use the nullable=False keyword to do this:

treatmentType_id = db.Column(db.Integer, db.ForeignKey('treatmentType.id'), nullable=False)

Now, when the default cascade logic executes, SQLAlchemy tries to set Treatment.treatmentType_id = None, and an IntegrityError is raised due to the non-null constraint being violated.

2. Using passive_deletes='all'

treatments = db.relationship('treatment', backref='treatmentType', passive_deletes='all')

When a TreatmentType gets delete, the passive_deletes='all' keyword on the treatments relationship "will disable the “nulling out” of the child foreign keys". It basically disables the default behavior outlined in the first paragraph above. So, when the ORM tries to delete the TreatmentType without first setting the child's Treatment.treatmentType_id = None, the database will throw an IntegrityError complaining that the child's ForeignKey references a non-existent parent!

*Note: The underlying database MUST support foreign keys to use this option