SEO

#1 (**permalink**) 05-13-2008, 06:14 PM

I found a query on how to find the primary key of a table:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = Database()
AND TABLE_NAME = "table_name"
AND COLUMN_KEY = "PRI"

Now..the question is, how do i find the columns that are foreign keys
in another table.

If this table has 4 columns that are foreign keys in other tables..how
do I find those via a query?

Thanks!
Chris

#2 (**permalink**) 05-16-2008, 01:40 PM

On Tue, 13 May 2008 19:20:23 +0200, Loony2nz <Loony2nz@gmail.com> wrote:

> I found a query on how to find the primary key of a table:
>
> SELECT COLUMN_NAME
> FROM information_schema.COLUMNS
> WHERE TABLE_SCHEMA = Database()
> AND TABLE_NAME = "table_name"
> AND COLUMN_KEY = "PRI"
>
> Now..the question is, how do i find the columns that are foreign keys
> in another table.
>
> If this table has 4 columns that are foreign keys in other tables..how
> do I find those via a query?

I have no idea wether this is always true, as I could not find the
documentation I wanted quickly enough, and this holds true enough for my
debugging purposes:

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,REFERENCED_COL UMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'table_name'
AND REFERENCED_TABLE_SCHEMA = DATABASE();
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How do you find the foreign keys in a table?