How do I see all foreign keys to a table or column?

Question

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.

Lusatian

Answer 1

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table>' \G

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>' \G

Basically, change REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the WHERE clause.

Pneumonic

this always gives me an empty set, while query proposed by Node below works fine – Jongjongleur

@Acute: Are you sure you are asking about the correct table? If Node's query work, then you are likely asking about the other direction (i.e., keys FROM mytable, not keys TO mytable.) This expecting you wrote '<table>' with the table name and without the '<' and '>'? – Pneumonic

Seems like I misunderstood you query, because I was querying for keys referencing FROM the <table> :) (yes, I wrote table name instead of "<table>" XD) – Jongjongleur

Unless you are sure your table name is unique, you'll probably want to restrict your query to a particular database as well. Change the where clause to this: where REFERENCED_TABLE_SCHEMA = 'mydatabase' and REFERENCED_TABLE_NAME = 'mytable' – Doerrer

This wont work in case of non root user even though the user have all permissions to the database – Shaikh

@DeepakRam This works perfectly for me with a privileged user that is not the root user. – Saito

Agreed, i only ever get an empty set here! Andy's answer worked best for me. – Nutritionist

Why is it required so much? Why not make it as easy as how you select rows? – Fasciation

This worked perfect for me. I have most privileges, but not root. You're a life saver. – Remarque

I added TABLE_SCHEMA to the list of columns (SELECT TABLE_SCHEMA, TABLE_NAME, …) which is useful in case of there are multiple databases that have tables relating to our table/column of interest. – Fowlkes

This worked great for me. Thank you! I ran this for my desired database and table from the SQL tab in phpMyAdmin and it gave me exactly what I needed. Was able to fix the broken constraints quickly and easily. – Coloratura

@Jongjongleur It's been a long time and by now you're an expert, but I want to point out the key difference: the query for tables in this answer selects rows by KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME getting results for a table that the fk points to. Whereas the query in Node's answer uses TABLE_CONSTRAINTS.TABLE_NAME and gets results a table that the fk belongs to. You can change REFERENCED_TABLE_NAME to TABLE_NAME in the table query above to get similar results, since KEY_COLUMN_USAGE has that column. – Ladonnalady

Not much use if you have to be the root user for it to work. Thumbs down. – Granddad

@VinkoVrsalovic What is the \G at the end? – Aurist

@Aurist the "\G" causes MySQL to display the results in vertical format, i.e., two columns where column 1 contains the row's column-names and column 2 contains their values. Much easier to read in many instances. It's great when not very many rows in the result but lots of columns. Otherwise, it will be horizontal format, with one very long row containing the column-names, word-wrapped as often as needed, then the rows of data, similarly word-wrapped. That's great when not too many columns but lots of rows. – Granddad

That's quite the useful queries, and not only for that kind of problem. – Ceremony

Answer 2

EDIT: As pointed out in the comments, this is not the correct answer to the OPs question, but it is useful to know this command. This question showed up in Google for what I was looking for, and figured I'd leave this answer for the others to find.

SHOW CREATE TABLE `<yourtable>`;

I found this answer here: MySQL : show constraints on tables command

I needed this way because I wanted to see how the FK functioned, rather than just see if it existed or not.

Palter

This shows all the constraints in <yourtable>, not all the constraints that point to <yourtable>. – Amery

As @Amery says, this is entirely wrong; it will show the foreign keys belonging to the specified table, but will not show foreign keys pointing TO the table, which is what the question asks for. No idea how this got 50 upvotes; I guess people ended up here when really they were looking for the answer to the opposite question, found their answer here anyway, and didn't bother reading the original question (or even its title) before upvoting. – Comedown

@MarkAmery : this is first result of display foreign keys mysql in google, may be that is why ;) – Gunsel

thisi actually shows all the constraints that exist, phpmyadmin will only show you the one pointing to your table. seems good enough to avoid duplicates with an ORM tool – Parturifacient

This is what I was looking for, so thanks for posting it even though it didn't answer OP's question. Never hurts to know how to look in both directions in a relationship! – Mervin

I love this kind of answers sir. – Requiem

Answer 3

If you use InnoDB and defined FK's you could query the information_schema database e.g.:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

Alurd

actually, that points the wrong direction. that query shows all the foreign keys pointing FROM 'mytable', not all foreign keys pointing TO 'mytable'. – Lusatian

This one works better in my case. I need to drop every foreign key constraint (and only those) from a table to be able to change the InnoDB engine MyISAM or NDB. – Horten

You can get foreign keys in both directions from the REFERENTIAL_CONSTRAINTS table – I have added another answer with the query. – Pompano

Answer 4

Posting on an old answer to add some useful information.

I had a similar problem, but I also wanted to see the CONSTRAINT_TYPE along with the REFERENCED table and column names. So,

1. To see all FKs in your table:

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE()
   AND i.TABLE_NAME = '<yourtable>';
   

2. To see all the tables and FKs in your schema:

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE();
   

3. To see all the FKs in your database:

   SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';
   

Remember!

This is using the InnoDB storage engine. If you can't seem to get any foreign keys to show up after adding them it's probably because your tables are using MyISAM.

To check:

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

To fix, use this:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Hedgehop

Those queries run much faster (from 2 secs to 0.0015 secs) if you specify k.TABLE_SCHEMA = DATABASE() and k.TABLE_NAME = '<table>' on the WHERE clause, as documented here dev.mysql.com/doc/refman/5.5/en/… – Egest

great answer. Do you have any solutions for MyISAM? – Sportscast

MyISAM does not support foreign keys, unfortunately. dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html – Hedgehop

Answer 5

As an alternative to Node’s answer, if you use InnoDB and defined FK’s you could query the information_schema database e.g.:

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND TABLE_NAME = '<table>'

for foreign keys from <table>, or

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND REFERENCED_TABLE_NAME = '<table>'

for foreign keys to <table>

You can also get the UPDATE_RULE and DELETE_RULE if you want them.

Pompano

I personally prefer this answer as using the REFERENTIAL_CONSTRAINTS table gives you the update and cascade rule. +1 – Janijania

Doing discovery about a table you shouldn't forget that foreign keys can be established BOTH ways! – Pm

Answer 6

Constraints in SQL are the rules defined for the data in a table. Constraints also limit the types of data that go into the table. If new data does not abide by these rules the action is aborted.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY';

You can view all constraints by using select * from information_schema.table_constraints;

(This will produce a lot of table data).

You can also use this for MySQL:

show create table tableName;

Lefebvre

Please add an explanation for long term value and to turn it into a quality answer that will be more likely to be upvoted. Answers should be able to help future visitors learn something and quickly determine if elements from your solution would apply to their own coding issues. – Node

Knowing names of the foreign keys doesn't really help in any meaningful way, shape or form. – Convenance

This helped tremendously. Other answers require you to supply names. This allows you to find all the foreign keys generically. It's exactly what I was looking for. – Woolworth

Answer 7

This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. drop contraint):

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

If you want to check tables in a specific database, at the end of the query add the schema name:

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'database_name';

Likewise, for a specific column name, add

and table_name = 'table_name

at the end of the query.

Inspired by this post here

Horologe

Answer 8

Using REFERENCED_TABLE_NAME does not always work and can be a NULL value. The following query can work instead:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';

Ugly

Answer 9

I'm reluctant to add yet another answer, but I've had to beg, borrow and steal from the others to get what I want, which is a complete list of all the FK relationships on tables in a given schema, INCLUDING FKs to tables in other schemas. The two crucial recordsets are information_schema.KEY_COLUMN_USAGE and information_schema.referential_constraints. If an attribute you want is missing, just uncomment the KCU., RC. to see what's available

SELECT DISTINCT KCU.TABLE_NAME, KCU.COLUMN_NAME, REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, UPDATE_RULE, DELETE_RULE #, KCU.*, RC.*
FROM information_schema.KEY_COLUMN_USAGE KCU
INNER JOIN information_schema.referential_constraints RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE TABLE_SCHEMA = (your schema name)
AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY KCU.TABLE_NAME, KCU.COLUMN_NAME;

Leucite

the most useful (detailed) answer for me – Halakah

Answer 10

It's often helpful to know the update and delete behaviour, which the other answers don't provide. So here goes.

SELECT cu.table_name,
       cu.column_name,
       cu.constraint_name,
       cu.referenced_table_name,
       cu.referenced_column_name,
       IF(rc.update_rule = 'NO ACTION', 'RESTRICT', rc.update_rule) AS update_rule,-- See: https://mcmap.net/q/55317/-what-is-mysql-39-s-default-on-delete-behavior
       IF(rc.delete_rule = 'NO ACTION', 'RESTRICT', rc.delete_rule) AS delete_rule -- See: https://mcmap.net/q/55317/-what-is-mysql-39-s-default-on-delete-behavior
FROM information_schema.key_column_usage cu
INNER JOIN information_schema.referential_constraints rc ON rc.constraint_schema = cu.table_schema
AND rc.table_name = cu.table_name
AND rc.constraint_name = cu.constraint_name
WHERE cu.referenced_table_schema = '<your schema>'
  AND cu.referenced_table_name = '<your table>';

Pickering

Super helpful when you are trying to debug cascade deletes, because the delete will fail on the first one it encounters and not report others that may also fail. This proactively shows you all the FKs that would block a row delete. – Conflagrant

Answer 11

A quick way to list your FKs (Foreign Key references) using the

KEY_COLUMN_USAGE view:

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 = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

This query does assume that the constraints and all referenced and referencing tables are in the same schema.

Add your own comment.

Source: the official mysql manual.

Embryonic

I don't think the footnote "This query does assume that the constraints and all referenced and referencing tables are in the same schema." is necessarily true? I have just used KEY_COLUMN_USAGE view (very helpful) to see cross-schema constraints – Leucite

Answer 12

I had a "myprodb" MySql database and for checking all foreign keys in this data base I used the following simple command.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'myprodb' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

I hope it help.

Footcandle

Answer 13

The solution I came up with is fragile; it relies on django's naming convention for foreign keys.

USE information_schema;
tee mysql_output
SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'database_name';
notee

Then, in the shell,

grep 'refs_tablename_id' mysql_output

Lusatian

Answer 14

If you also want to get the name of the foreign key column:

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, 
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, 
       k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
  FROM information_schema.TABLE_CONSTRAINTS i 
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
       ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 WHERE i.TABLE_SCHEMA = '<TABLE_NAME>' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
 ORDER BY i.TABLE_NAME;

Nutritionist

Thanks great solution! I needed the column name as well, to get it add: 'k.COLUMN_NAME' – Collegian

Answer 15

To find all tables containing a particular foreign key such as employee_id

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('employee_id')
AND TABLE_SCHEMA='table_name';

Vidal

This shows tables with those columns. The columns aren't necessarily foreign keys. – Gipsy

Answer 16

I needed a bird's-eye-view on the relationships among the tables (to use in an ORM). Using the suggestions from this page, and after experimenting, I've put together the following query:

SELECT
    KCU.CONSTRAINT_NAME,
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.REFERENCED_TABLE_NAME,
    KCU.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
        ON
                COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
            AND COLS.TABLE_NAME   = KCU.TABLE_NAME
            AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
        KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
    AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    KCU.TABLE_NAME,
    COLS.ORDINAL_POSITION

It returns just what I need, and in the order that I want.

I also do little processing on the result (turn it into a some kind of dictionary), so that it's ready to be used for creating an aggregate.

Sooty