MySQL: How to determine foreign key relationships programmatically?

Question

Similar to this question but for MySQL....

How can I programmatically determine foreign key references in MySQL (assuming InnoDB)? I can almost get them with:

SHOW TABLE STATUS WHERE Name = 'MyTableName';

...but alas, the comment column which seems to contain some of this info gets truncated so I can't rely on it. There must be some other way...

I'd be happy with a C API call, a SQL statement, anything--I just need something that consistently works.

Note: I've also considered parsing the results of a "SHOW CREATE TABLE MyTableName" statement, but I'm really hoping there's something simpler.

score 29 · Accepted Answer · answered Nov 07 '08 at 22:51

29

There are two tables you can query to get this information: INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

Here's a query from the comments on the latter page linked above, which demonstrates how to get the info you seek.

SELECT CONCAT( table_name, '.', column_name, ' -> ', 
  referenced_table_name, '.', referenced_column_name ) AS list_of_fks 
FROM INFORMATION_SCHEMA.key_column_usage 
WHERE referenced_table_schema = 'test' 
  AND referenced_table_name IS NOT NULL 
ORDER BY table_name, column_name;

Use your schema name instead of 'test' above.

answered Nov 07 '08 at 22:51

Bill Karwin

538,548
86
673
828

A pretty slow query.... `/* 0 rows affected, 22 rows found. Duration for 1 query: 2,996 sec. */` – V-Light Aug 21 '12 at 09:26
@V-Light, you have another solution? – Bill Karwin Aug 21 '12 at 16:40

score 7 · Answer 2 · edited Aug 21 '17 at 06:34

Here you have a little improvement over the @bill solution:

SELECT CONSTRAINT_SCHEMA AS db,
       CONCAT (
           TABLE_NAME,
           '.',
           COLUMN_NAME,
           ' -> ',
           REFERENCED_TABLE_NAME,
           '.',
           REFERENCED_COLUMN_NAME
       ) AS relationship 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'your_table_name'
ORDER BY CONSTRAINT_SCHEMA,
         TABLE_NAME,
         COLUMN_NAME;

In this case I was filtering by relationships with the "your_table_name" fields and seeing from which database the relationship comes.

score 2 · Answer 3 · answered Nov 07 '08 at 22:12

2

Try INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

answered Nov 07 '08 at 22:12

Cade Roux

88,164
40
182
265

I don't seem to have that table, though I do have INFORMATION_SCHEMA.TABLE_CONSTRAINTS which has most (but not quite all) the info I need. Thanks anyways! – Drew Hall Nov 08 '08 at 02:29

score 0 · Answer 4 · answered Feb 24 '20 at 18:07

based on Bill Karwin answered, I used this solution to get all the info I needed, included on_delete and on_update rules:

SELECT kcu.referenced_table_schema, kcu.constraint_name, kcu.table_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name, 
       rc.update_rule, rc.delete_rule 
FROM INFORMATION_SCHEMA.key_column_usage kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on kcu.constraint_name = rc.constraint_name
WHERE kcu.referenced_table_schema = 'db_name' 
AND kcu.referenced_table_name IS NOT NULL 
ORDER BY kcu.table_name, kcu.column_name

score -1 · Answer 5 · edited Aug 21 '17 at 06:20

-1

SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'table_name'
      AND TABLE_SCHEMA = 'table_schema';

edited Aug 21 '17 at 06:20

simhumileco

31,877
16
137
115

answered May 31 '16 at 11:59

Bkeshh Mhrjn

7
1

While your query seem to work, please provide the community with a little statement on how it works. – cb0 May 31 '16 at 13:13

MySQL: How to determine foreign key relationships programmatically?

5 Answers5

Linked