I just did a quick search and it would appear that MySQL supports "ON DELETE CASACDE", so this should apply to both MySQL and PostgreSQL, if my google source is correct.
If you look in the create_tables.postgresql script in the dbmail tree, you will notice that all the user_idnr foreign key constraints have "ON DELETE CASCADE". I noticed that the corresponding MySQL script does not have this, but the MySQL InnoDB does have this feature. So maybe it's only available in InnoDB. What it means is if the foreign key reference is removed, the delete process will cascade through the database. (at least that's my understanding.) So because I have this on all the user_idnr's and the message_idnr's when I do a "delete from dbmail_users where user_idnr = 592" the database takes care of deleting the aliases, mailboxes, messages, physmessages and finally the actual user. In practice I've never had a problem with this. I'm deleting users often and each night when I run dbmail-util I never have any unconnected messages, etc. This may explain it better: http://www.ilook.fsnet.co.uk/ora_sql/sqlhlp21.htm That's the beauty of foreign keys, let them do the work for you. -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: DK [mailto:[EMAIL PROTECTED] Sent: Monday, July 10, 2006 12:34 PM To: DBMail mailinglist Subject: Re: [Dbmail] Delete accounts On 7/10/06, Niblett, David A <[EMAIL PROTECTED]> wrote: > I'm guessing that your are using MySQL and cascade delete > is not an option? > > With mine, I just delete from the dbmail_user table and everything > else goes with it. One simple query. > > -- > David A. Niblett | email: [EMAIL PROTECTED] > Network Administrator | Phone: (352) 334-3400 > Gainesville Regional Utilities | Web: http://www.gru.net/ > Hi Neighbor, This has become an interesting thread. I don't understand a word your saying but it sounds like something I like to know more about. How does everything go with it? PS: I am out in Ormond Beach by Daytona! -- Demi
