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
