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

Reply via email to