List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 4 2009 2:24am
Subject:Re: Algorithm for resolving foreign key dependencies?
View as plain text  
Donna,

 >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).

Yep that's a tree but it ain't the one he needs. He can build a tree of 
the FK children of db.tbl by modifying Listing 7 in 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#edge_list 
to use a version of the followings query as the algorithm walks down the 
tree:

|SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY' 
 AND u.referenced_table_schema='db'
 AND u.referenced_table_name = 'tbl'
ORDER BY c.table_schema,u.table_name;  |

PB

-----

ddevaudreuil@stripped wrote:
> 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.
>>     
>
>
>   
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
>
>   

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