On Jun 22, 2010, at 9:27 AM, exhuma.twn wrote:
> 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?
I can't answer very specifically to what the case here is since you didn't
provide mappings or example code. I'm assuming you're using the ORM and we're
talking about relationship() here. It appears like holder_id and owner_id are
handled by different relationships() altogether. Assuming that's the case,
affecting the "holder" relationship would have no affect on owner_id.
So if the case is that "owner" is being detached and you'd like "item" to be
deleted, you need to be using "delete, delete-orphan" cascade so that the row
is deleted, rather than dereferenced from the parent.
http://www.sqlalchemy.org/docs/session.html#cascades
However, I see you have RESTRICT set on owner_id. If you would like to instead
entirely prevent the modification of owner_id when the parent object is
deleted, and allow referential integrity to raise an error, you should set
passive_deletes to "all", which will disable the "nulling out" on delete of the
parent. This is documented under relationship at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html?#sqlalchemy.orm.relationship
.
> 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?
>
> Here's my model for those two tables:
>
> contact = Table( 'contact', metadata,
> Column( 'contact_id', Integer, primary_key=True,
> nullable=False),
> Column( 'label', Unicode(64), nullable=False ),
> )
>
> item = Table( 'item', metadata,
> Column( 'item_id', Integer, nullable=False, primary_key=True ),
> Column( 'label', Unicode(64) ),
> Column( 'barcode', String(64) ),
> Column( 'comment', UnicodeText() ),
> Column( 'owner_id', Integer, ForeignKey('contact.contact_id',
> onupdate="CASCADE", ondelete="RESTRICT"), nullable=False),
> Column( 'holder_id', Integer, ForeignKey('contact.contact_id',
> onupdate="CASCADE", ondelete="SET NULL"), nullable=True),
> )
>
> --
> 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.
>
--
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.
