Prevent delete if object in some collection

Question

In SQLAlchemy is it possible to prevent (or specifically raise a custom Exception) the deletion of an object that is part of a collection in a many-to-many relationship.

For example below I have a Party and Person. Any Party can have many Persons and any Person can go to many Parties.

If a Person is recorded as going to one or more Parties I want to prohibit that Person from being deleted.

party_person_ass_table = db.Table('party_person', db.Model.metadata,
                     db.Column('party_id', db.ForeignKey('party.id'), primary_key=True),
                     db.Column('person_id', db.ForeignKey('person.id'), primary_key=True))

class Party(db.Model):
    ... 
    people = db.relationship('Person', secondary=party_person_ass_table, back_populates='parties')

class Person(db.Model):
    ... 
    parties = db.relationship('Party', secondary=party_person_ass_table, back_populates='people')

Reading the docs it seems cascade deleting from the secondary table is automatically set (https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html). I wonder if a solution to generate an IntegrityError would be to add a nullable=False to the person_id column in the secondary association table and then somehow prevent a cascade delete from impacting the secondary table?

I’d just make `party_person_ass_table.person_id` [ON DELETE RESTRICT](https://stackoverflow.com/a/8073114/6560549). — SuperShoot, Apr 14 '19 at 22:40
@SuperShoot sounds sensible so I changed to `db.Column('person_id', db.ForeignKey('person.id', ondelete='RESTRICT'), primary_key=True))` but it had no effect. I am using SQLite3 and am executing `PRAGMA foreign_keys=ON`. I destroyed and recreated my DB, so not sure if I am missing something else? — Attack68, Apr 15 '19 at 07:24
I couldn't get the above to work. Instead, I tested putting `viewonly=True` inside `parties=db.relationship(..)`. Now when a `Person` is deleted which exists in the secondary table an `IntegrityError ForeignKey constraint` exception is raised, which I think is because the delete cascade is not followed and therefore creates the break. — Attack68, Apr 15 '19 at 15:05
As a further comment to why your solution above didn't work is becuase I think that SQLalchemy instructs the secondary table rows deletion (as an automatic cascade) before the physical object delete event. Therefore `ondelete='RESTRICT'` has no effect since those objects have been previously and individually deleted. — Attack68, Apr 15 '19 at 15:08

score 1 · Accepted Answer · answered Apr 20 '19 at 20:14

adding the tag viewonly=True to:

parties = db.relationship('Party', secondary=party_person_ass_table, back_populates='people', viewonly=True)

decouples Person from the party_person_ass_table and when modified it does not automatically delete the row in the association table, which breaks database integrity.

This is also why the commented 'ON DELETE RESTRICT' fails because it isnt an inherent cascade, but SQLAlchemy specifically deletes these rows as part of its function so these hooks aren't called in the usual way.

Prevent delete if object in some collection

1 Answers1