Hi Are you using MYSQL ? and its InnoDB engine ? MYISAM don't provide integrity checking.
On 19 juin, 13:27, Michel Albert <[email protected]> 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? 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.
