(pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails...') in Flask

Question

I'm aware that there are many questions about the error in the title, but I could't find a suitable solution. My problem is that while deleting a row using Session.delete() it's throwing

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`transport`.`driver`, CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `truckcompany` (`id`))') [SQL: 'DELETE FROM truckcompany WHERE truckcompany.id = %(id)s'] [parameters: {'id': 4}]

Models:

class Truck_company(Base):

    __tablename__ = 'truckcompany'

    id = Column(BigInteger, primary_key=True)

class Driver(Base):

    __tablename__ = 'driver'

    id = Column(BigInteger, primary_key=True)
    owner_id = Column(BigInteger, ForeignKey('truckcompany.id'))
    owner = relationship(Truck_company)

The view with the failing delete:

@app.route('/values/deleteuser/<int:id>', methods=['POST', 'GET'])
def delete_truck(id):
    value_truckcompany = sqlsession.query(Truck_company).filter(Truck_company.id == id).first()
    if value_truckcompany:
        sqlsession.delete(value_truckcompany)
        sqlsession.commit()
        return redirect('/static/truckcompanyview', )

Answer 1

Why

In your Driver model there's a foreign key constraint referencing Truck_company:

class Driver(Base):
    ...
    owner_id = Column(BigInteger, ForeignKey('truckcompany.id'))

You've omitted the ON DELETE action, so MySQL defaults to RESTRICT. Also there are no SQLAlchemy ORM relationships with cascades that would delete the related drivers. So when you try to delete the truck company in the view, the DB stops you from doing that, because you'd violate your foreign key constraint and in other words referential integrity. This is an issue with how you've modeled your DB, not Flask etc.

What can I do

The most important thing to do – when you're creating your model – is to decide what would you like to happen when you delete a truck company with related drivers. Your options include, but are not limited to:

1. Deleting the drivers also.
2. Setting their owner_id to NULL, effectively detaching them. This is what SQLAlchemy does, if an ORM relationship is present in its default configuration in the parent.

It is also a perfectly valid solution to restrict deleting parent rows with children, as you've implicitly done.

You've expressed in the comments that you'd like to remove the related drivers. A quick solution is to just manually issue a DELETE:

# WARNING: Allowing GET in a data modifying view is a terrible idea.
# Prepare yourself for when Googlebot, some other spider, or an overly
# eager browser nukes your DB.
@app.route('/values/deleteuser/<int:id>', methods=['POST', 'GET'])
def delete_truck(id):
    value_truckcompany = sqlsession.query(Truck_company).get(id)

    if value_truckcompany:
        sqlsession.query(Driver).\
            filter_by(owner=value_truckcompany).\
            delete(synchronize_session=False)
        sqlsession.delete(value_truckcompany)
        sqlsession.commit()
        return redirect('/static/truckcompanyview', )

This on the other hand fixes this one location only. If you decide that a Driver has no meaning without its Truck_company, you could alter the foreign key constraint to include ON DELETE CASCADE, and use passive deletes in related SQLAlchemy ORM relationships:

class Truck_company(Base):
    ...
    # Remember to use passive deletes with ON DELETE CASCADE
    drivers = relationship('Driver', passive_deletes=True)

class Driver(Base):
    ...
    # Let the DB handle deleting related rows
    owner_id = Column(BigInteger, ForeignKey('truckcompany.id',
                                             ondelete='CASCADE'))

Alternatively you could leave it to the SQLAlchemy ORM level cascades to remove related objects, but it seems you've had some problems with that in the past. Note that the SQLAlchemy cascades define how an operation on the parent should propagate to its children, so you define delete and optionally delete-orphan on the parent side relationship, or the one-to-many side:

class Truck_company(Base):
    ...
    # If a truck company is deleted, delete the related drivers as well
    drivers = relationship('Driver', cascade='save-update, merge, delete')

In your current model you have no relationship defined from Truck_company to Driver, so no cascades take place.

---

Note that modifying Driver such as:

class Driver(Base):
    ...
    owner_id = Column(BigInteger, ForeignKey('truckcompany.id',
                                             ondelete='CASCADE'))

will not magically migrate the existing DB table and its constraints. If you wish to take that route, you'll have to either migrate manually or using some tool.