Why not put a flag variable (tinyint or enum) in your ABA table and instead of deleting the records, just mark them as no longer valid?
--jeff ----- Original Message ----- From: "David Felio" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, February 27, 2002 11:49 AM Subject: Re: flexible foreign keys? > When I try to delete a row from the parent table and the key is in use in > the child table, I get: > > ERROR 1217: Cannot delete a parent row: a foreign key constraint fails > > So it doesn't cascade and delete rows in the child table (assuming I am > interpreting "cascade on delete" correctly), but it doesn't let me delete > the parent row either. I can certainly understand the restriction, but it > puts me in a bit of a bind on the db design. I don't want the child to be > deleted anyway, if the parent is deleted. I just want to delete the parent > row. > > On Wednesday, February 27, 2002, at 01:37 PM, Jeff Kilbride wrote: > > > InnoDB doesn't support the CASCADE functionality of foreign keys, so it's > > possible deleting the key from the parent table won't have any effect on > > existing transactions in the child. It may only prevent new records from > > being inserted with that key -- which is essentially what you want. Most > > DBs > > that support CASCADE give you the option of turning it off. > > > > http://www.mysql.com/doc/S/E/SEC445.html > > > > Have you tried entering a bogus ACH number and a corresponding transaction, > > and then deleting the key? I don't use InnoDB, so I don't know if it will > > work, but it's worth a try... > > > > Thanks, > > --jeff > > > > ----- Original Message ----- > > From: "David Felio" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, February 27, 2002 8:21 AM > > Subject: flexible foreign keys? > > > > > >> I have a MySQL InnoDB table for recording checking account transactions > > and > >> it currently has a foreign key on the routing number referencing a local > >> copy of the fed ach routing number database. It works a little too well, > > in > >> that a routing number may be good today, but not tomorrow. I would like > > for > >> the foreign key to be there to make sure an entry with a bad routing > > number > >> doesn't get entered, but I also will need to delete routing numbers from > >> the local fed db when they are no longer good and if I have a transaction > >> that previously used the routing number I won't be able to delete it. So, > >> for example, on 2/27/2002 I do a transaction with routing number > > 123456789. > >> The foreign key shows that that is a valid routing number so it lets the > >> insert go through. On 2/29/2002 (or any other day after the transaction) > >> the fed removes 123456789 from its list of valid routing numbers. Now I > >> want to delete it from my local copy, but can't because it is linked via > >> the foreign key to the transaction on 2/27/2002. > >> > >> Can someone help me with a solution here? > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
