Well, honestly, by deleting the record in the parent, you're already defeating the purpose of the foreign key by corrupting the referential integrity of the parent to child relationship. The basic reasoning behind foreign keys is to maintain this integrity and prevent this type of corruption. So, you're essentially asking the DB to break a rule you told it to enforce.
The typical scenario, for DB's that support it, is to delete all the existing child records when a parent record is deleted in order to maintain referential integrity. However, the commercial DB engines I've worked with that support this CASCADE functionality, usually allow you to ignore the problems this creates -- but it's still an exception to the norm and usually not recommended. Unfortunately, MySQL/InnoDB doesn't support CASCADE or, obviously from your test, the ability to ignore this exception. Even if it did support it, it wouldn't necessarily be the correct use of foreign keys. If parent records can disappear in the future without removing child records, is it really a parent-child relationship or something else? Personally, I would lift the foreign key constraint and implement your ABA numbers as a lookup table with a flag variable to determine their validity. That may be your only option with MySQL, until InnoDB supports CASCADE. --jeff ----- Original Message ----- From: "David Felio" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Thursday, February 28, 2002 7:16 AM Subject: Re: flexible foreign keys? > I considered that, but wouldn't that defeat the purpose of the foreign key? > I would then have to do a select on the ABA table to determine the status > of the routing number, and the foreign key would only restrict entries to > those that were at one point valid, not those that are currently valid. > > On Wednesday, February 27, 2002, at 07:31 PM, Jeff Kilbride wrote: > > > 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 > > > > > > 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
