List:General Discussion« Previous MessageNext Message »
From:ddevaudreuil Date:February 4 2009 12:02am
Subject:Re: Algorithm for resolving foreign key dependencies?
View as plain text  
Try looking at the  information_schema.KEY_COLUMN_USAGE table (where
referenced_table_schema is not null).  It will show you the FK
relationships.  You could then create a tree that you could use to find the
hierarchy.  For that, I suggest looking at
http://www.artfulsoftware.com/infotree/mysqlquerytree.php.  The
information_schema table is already sort of an edge-list, although each
node is made up of the tuple (table_schema, table_name, column_name) or
(referenced_table_schema, referenced_table_name, referenced_column_name).


Donna

news <news@stripped> wrote on 02/03/2009 05:38:34 PM:

> Andy Shellam wrote:
> > Am I missing something here?  (It is late after a long day, I admit!)
>
> Only something I forgot to mention.
>
> All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's
> response to a foreign key violation is to spit out an error message to
the
> effect of "I'm sorry, Dave, I can't let you do that."
>
> The problem is, the target platform doesn't use foreign keys for
performance
> reasons. I want to use foreign keys in development as a
bug-trappingmethod --
> I'd rather see an FK violation error in development than get an angry
email
> from a customer asking why there's a part listed that doesn't seem to
have a
> manufacturer.
>
> The plan was to write a code-generator that would generate all the
database
> code for me, then I could deal with the page templates and display logic
> myself (thus eliminating ~80% of the boring, repetitive work). I want the

> generated code to handle foreign keys itself, rather than relying on
> the database.
>
> As I said above, if foreign key constraints didn't slow things down
markedly,
> I'd use them in production. Based on the (admittedly limited) testing
I've
> done, application-side FK enforcement is considerably faster than using
ON
> DELETE CASCADE and letting MySQL deal with the foreign keys.
>
> I don't like writing database code by hand (it all follows a standard
> template), so I figured I'd write a program to do it for me. "Work
> smarter not
> harder" and all that :)
>
> Thanks,
> --
> Phil.
> usenet08@stripped
> http://www.philpem.me.uk/
> If mail bounces, replace "08" with the last two digits of the current
year.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?
> unsub=ddevaudreuil@stripped
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>

>
> CONFIDENTIALITY NOTICE:This email is intended solely for the person
> or entity to which it is addressed and may contain confidential
> and/or protected health information.  Any duplication,
> dissemination, action taken in reliance upon, or other use of this
> information by persons or entities other than the intended recipient
> is prohibited and may violate applicable laws.  If this email has
> been received in error, please notify the sender and delete the
> information from your system.  The views expressed in this email are
> those of the sender and may not necessarily represent the views of
> IntelliCare.

Thread
Algorithm for resolving foreign key dependencies?Philip Pemberton3 Feb
  • Re: Algorithm for resolving foreign key dependencies?Andy Shellam3 Feb
    • Re: Algorithm for resolving foreign key dependencies?Philip Pemberton3 Feb
      • Re: Algorithm for resolving foreign key dependencies?ddevaudreuil4 Feb
        • Re: Algorithm for resolving foreign key dependencies?Peter Brawley4 Feb
  • Re: Algorithm for resolving foreign key dependencies?Andrew Garner3 Feb