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