49

My User model has a relationship to the Address model. I've specified that the relationship should cascade the delete operation. However, when I query and delete a user, I get an error that the address row is still referenced. How do I delete the user and the addresses?

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    addresses = db.relationship('Address', cascade='all,delete', backref='user')

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey(User.id))

db.session.query(User).filter(User.my_id==1).delete()

IntegrityError: (IntegrityError) update or delete on table "user" violates foreign key constraint "addresses_user_id_fkey" on table "address"
DETAIL:  Key (my_id)=(1) is still referenced from table "address".
 'DELETE FROM "user" WHERE "user".id = %(id_1)s' {'id_1': 1}
davidism
  • 121,510
  • 29
  • 395
  • 339
AndroidDev
  • 15,993
  • 29
  • 85
  • 119

1 Answers1

107

You have the following...

db.session.query(User).filter(User.my_id==1).delete()

Note that after "filter", you are still returned a Query object. Therefore, when you call delete(), you are calling delete() on the Query object (not the User object). This means you are doing a bulk delete (albeit probably with just a single row being deleted)

The documentation for the Query.delete() method that you are using says...

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

As it says, running delete in this manner will ignore the Python cascade rules that you've set up. You probably wanted to do something like..

user = db.session.query(User).filter(User.my_id==1).first()
db.session.delete(user)

Otherwise, you may wish to look at setting up the cascade for your database as well.

davidism
  • 121,510
  • 29
  • 395
  • 339
Mark Hildreth
  • 42,023
  • 11
  • 120
  • 109
  • Thank you so mcuh mark. For the first method to work, what changes would i have to make ? – AndroidDev Oct 08 '13 at 10:35
  • @BadLuckBrian: This depends on the database you are using. Read the documentation I linked to at the end of my question for more info. Also, read the documentation for your database. – Mark Hildreth Oct 08 '13 at 10:39
  • I am using posgress on Heroku. I added the cascade option to the Foregien Key column in the Addresses tables. Maybe Flask-Migrate may not have noticed this constraint had been added ? Could that of been possible ? – AndroidDev Oct 08 '13 at 10:48
  • 1
    See [this question](http://stackoverflow.com/questions/10356484/how-to-add-on-delete-cascade-constraints). Not familiar with Postgres, but might be that you can't alter the schema to add the cascade after the foreign key constraint was already created. – Mark Hildreth Oct 08 '13 at 10:55