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?
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
"TurboGears" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en.
