> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of exhuma.twn > Sent: 22 June 2010 14:27 > To: sqlalchemy > Subject: [sqlalchemy] Referential integrity actions are not > doing what I want > > Hi, > > I have a table of "items", where each "item" can be "owned" by one > person, and "held" by someone else. I want the "owner" to be > compulsory (not nullable), and the holder to be optional (nullable). > To model this I have two tables, one for contacts and one for items. > The item table has two fields "owner_id" and "holder_id". Bot are > references to the "contact" table and have the "on delete" rule set to > "restrict" and "set null" respectively. > > The problem is that when I want to delete the contact attached to the > "holder_id" column, it seems that SA tries to set *both* references to > null. It should not do this! For example: If you have an item which > has an owner_id "1" and a holder_id "2", then deleting the contact > with ID "2" will cause the following query: > > 'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE > item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id': > 10, 'owner_id': None} > > First of all, *why* is SA issuing this query at all? A delete query > would suffice. The ref. integrity should be handled by the DB, > shouldn't it? More importantly, it updates both owner_id and > holder_id. But as previously said, owner_id=1 and holder_id=2. So > deleting contact #2 should only trigger - if at all - an update query > to set holder_id to null. > > Any ideas as to what I am doing wrong here?
There are various ways of configuring SA's behaviour when you delete objects with relationships. You may want to refer to these pages in the docs: http://www.sqlalchemy.org/docs/mappers.html#using-passive-deletes http://www.sqlalchemy.org/docs/session.html#cascades http://www.sqlalchemy.org/docs/ormtutorial.html#deleting Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
