CODEWITHSUNDEEP
sqlalchemymany-to-many
Attack68
Attack68

Reputation: 4785

Prevent delete if object in some collection

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?

Upvotes: 0

Views: 543

Answers (1)

Attack68
Attack68

Reputation: 4785

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.

Upvotes: 1

Related Questions