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? > > David Felio > Software Developer > Information Network of Arkansas > http://www.AccessArkansas.org > > > --------------------------------------------------------------------- > 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
