> > > OK so, you have: > > Page.user_relationships -> collection of PageToUser > > PageToUser -> single User > > then, you are saying : > > some_page.user_relationships = [] > session.flush() > > What SQL would you expect this to produce? After a flush, what would the > rows in your page_to_user table look like? > > I would expect it to delete entries, resulting in no rows for that page_id DELETE FROM Page_To_User WHERE page_id = ?
Perhaps it tries this instead? UPDATE Page_To_User SET page_id = NULL WHERE page_id = ? > > What I'm having trouble understanding is what is cascading from what to > what when deleting from this page.user_relationships collection. It doesn't > cascade from page, because I am not deleting any pages, and it doesn't > cascade from users, because no users are deleted. > > > “delete-orphan” means when an item is removed from a collection, in this > case a PageToUser object, it is marked as deleted. > So by default does page.user_relationships call the above UPDATE call and set to null or something? > In the database table definition, there doesn't have to be any cascade > settings at all for the constraints and it will still work fine. > > > OK you need to show what “works fine” is - how the Page.user_relationships > collection can be empty on a particular Page object, yet there are > PageToUser objects in the database which refer to that Page (or if you > think the PageToUser row still exists, but doesn’t point to any Page, show > me how that looks). What’s in the database? > > In my mind, Page.user_relationship is a collection of PageToUser objects, so by emptying that collection, all those objects are deleted from Page_To_User. It doesn't have anything to do with the Page object itself though - only the associations in PageToUser. The following table definitions work without the CASCADE being used on FK at all (it may prevent deletions of Page or User rows due to FK constraints, but not in this example since we don't ever delete those rows). CREATE TABLE Page ( int page_id not null auto increment, varchar(255) title, primary key (page_id) ) Engine=InnoDB; CREATE TABLE User ( int user_id not null auto increment, varchar(255) name, primary key (user_id) ) Engine=InnoDB; CREATE TABLE Page_To_User ( int page_id not null, int user_id not null, int relationship_id not null, primary key (page_id, user_id, relationship_id), foreign key (page_id) references Page(page_id), foreign key (user_id) references User(user_id) ) Engine=InnoDB; Above, there isn't any ON DELETE CASCADE specified for any of the foreign keys. My understanding is if there were, foreign key (page_id) references Page(page_id) ON DELETE CASCADE then, if I delete row with that page_id from Page, then it will cascade from Page to Page_To_User and delete the appropriate row. If ON DELETE CASCADE is missing, it will complain if I try to delete that Page row, because there are FK constraints. But issuing a simple ' DELETE FROM Page_To_User WHERE page_id = ? ' shouldn't require any cascading, at least in SQL. Maybe the cascade keyword in SQLAlchemy does not exactly mean cascade on a table definition? -Russ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
