Assuming "user_id" is a surrogate primary key, I dont see any need for onupdate="CASCADE" to be used here. Additionally, ondelete="CASCADE" on your hat.user_id column implies that "hat" will be deleted when a "user" entry is deleted - however your relation has this set up on the many-to-one side indicating that a "user" would be deleted when a "hat" is deleted.
I think this is the full setup you're looking for: users_table = Table('tg_user', metadata, Column('user_id', Integer, primary_key=True), Column('user_name', Unicode(16), unique=True), Column('created', DateTime, default=datetime.now), mysql_engine='InnoDB', ) hat_table = Table('hat', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(100)), Column('user_id', Integer, ForeignKey('tg_user.user_id', ondelete='CASCADE')), mysql_engine='InnoDB', ) class User(object): pass class Hat(object): pass mapper(User, users_table) mapper(Hat, hat_table, properties = { 'user': relation(User, backref=backref("hats", cascade="all, delete, delete-orphan", passive_deletes=True) ), } ) On Dec 5, 2008, at 1:42 PM, James wrote: > > Thanks for your quick response! Unfortunately I made the changes you > suggest and I still get the UPDATE commands being sent to the children > before the DELETE on the parent. My model is now: > > users_table = Table('tg_user', metadata, > Column('user_id', Integer, primary_key=True), > Column('user_name', Unicode(16), unique=True), > Column('created', DateTime, default=datetime.now), > mysql_engine='InnoDB', > ) > > hat_table = Table('hat', metadata, > Column('id', Integer, primary_key=True), > Column('name', Unicode(100)), > Column('user_id', Integer, ForeignKey('tg_user.user_id', > onupdate='CASCADE', ondelete='CASCADE')), > mysql_engine='InnoDB', > ) > > class User(object): > pass > > class Hat(object): > pass > > mapper(User, users_table) > > mapper(Hat, hat_table, > properties = { > 'user': relation(User, backref="hats", cascade="all, delete, > delete-orphan", passive_updates=True, passive_deletes=True), > } > ) > > And I get the same pattern in the log: > BEGIN > UPDATE hat SET user_id=%s WHERE hat.id = %s > [None, 1L] > DELETE FROM tg_user WHERE tg_user.user_id = %s > [1L] > COMMIT > > The ondelete and cascade arguments I have should be working in this > situation, right? > > Thanks again, > James > > On Dec 5, 11:36 am, Michael Bayer <[EMAIL PROTECTED]> wrote: >> actually, use passive_deletes=True, not 'all'. It will issue >> DELETEs >> only for collections that are already loaded, this doesn't break >> anything and prevents unnecessary SELECTs of unloaded collections. >> The True setting is needed so that the session can update the state >> of >> those collections during the flush process. >> >> On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote: >> >>> use the "passive_updates=True, passive_deletes='all'" flags. These >>> are described >>> athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla >>> ... >>> . >> >>> On Dec 5, 2008, at 11:42 AM, James Brady wrote: >> >>>> Hi all, >>>> I'm trying to get deletes and updates cascaded down from a parent >>>> object to the child objects (connected by ForeignKey). >> >>>> It all seems pretty simple in the docs, but I can't get it to work! >>>> I'm using MySQL with the InnoDB engine, and have played with all >>>> the variation of the onupdate, ondelete and cascade arguments I can >>>> think of. >> >>>> The problem is that immediately before the DELETE command is sent >>>> to MySQL, there are UPDATE commands nulling out the foreign key >>>> references of the child objects, so MySQL doesn't trigger it's ON >>>> DELETE CASCADE action, and for some reason cascade="all, delete- >>>> orphan" doesn't clean up the children with NULL FKs. >> >>>> Here's a simplified model: >>>> users_table = Table('tg_user', metadata, >>>> Column('user_id', Integer, primary_key=True), >>>> Column('created', DateTime, default=datetime.now), >>>> mysql_engine='InnoDB', >>>> ) >> >>>> hat_table = Table('hat', metadata, >>>> Column('id', Integer, primary_key=True), >>>> Column('name', Unicode(100)), >>>> Column('user_id', Integer, ForeignKey('tg_user.user_id'), >>>> onupdate='CASCADE', ondelete='CASCADE'), >>>> mysql_engine='InnoDB', >>>> ) >> >>>> class User(object): >>>> pass >> >>>> class Hat(object): >>>> pass >> >>>> mapper(User, users_table) >> >>>> mapper(Hat, hat_table, >>>> properties = { >>>> 'user': relation(User, backref="hats", cascade="all, >>>> delete, delete-orphan"), >>>> } >>>> ) >> >>>> And the log from SA when I do a User.delete: >>>> BEGIN >>>> UPDATE hat SET user_id=%s WHERE hat.id = %s >>>> [None, 1L] >>>> DELETE FROM tg_user WHERE tg_user.user_id = %s >>>> [1L] >>>> COMMIT >> >>>> Any help would be much appreciated! >> >>>> James > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---