On Jun 21, 4:03 pm, aspineux <[email protected]> wrote: > 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), > > )
In my case it's Postgres. But as Diez correctly suggested, this might be better suited on the SA list. I'll copy it over there. -- 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.
