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
-----
ddevaudre...@intellicare.com 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 <n...@ger.gmane.org> 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.
usene...@philpem.me.uk
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=ddevaudre...@intellicare.com
--
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
