On Sat, 2007-07-14 at 19:07 +0200, Paul J Stevens wrote: > Aaron Stone wrote: > > On Sat, 2007-07-14 at 11:09 +0200, Paul J Stevens wrote: > >> Aaron Stone wrote: > > > >>> You can't just update the dbmail_users table to change the user_idnr > >>> because the foreign keys in the mailboxes table will cause the user's > >>> mailboxes to be detached and then removed. > >> Wrong, *because* of the constraints it is in fact absolutely necessary > >> to change the user_idnr in the dbmail_users table. It's what I do, > >> whenever I migrate to ldap. Works perfectly, but only if you have the > >> proper constraints in place. They are part of the default schema. > > > > I don't understand this. If there are current entries in dbmail_users, > > and they have user_idnr's, and those are targets of a FK in the dbmail_ > > mailboxes table... > > > > CREATE TABLE dbmail_mailboxes ( > > ... > > FOREIGN KEY owner_idnr_fk (owner_idnr) > > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE > > ); > > > > ....when you change the user_idnr in dbmail_users, doesn't it cause a > > cascading delete of those entries in the mailboxes table? > > Nope. You misunderstand. If you delete a user entry, the delete > cascades. But if you update a user entry, the update cascades. So in > this case, if you update a user_idnr in the users table, all > corresponding entries in the mailboxes table follow suit: the > back-reference stays intact.
Ugh, *duh* - ON UPDATE CASCADE. Thanks for connecting the dots. Aaron _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
