How do I see all foreign keys to a table or column?
Solution 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 = '<database>' AND
REFERENCED_TABLE_NAME = '<table>';
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 = '<database>' AND
REFERENCED_TABLE_NAME = '<table>' AND
REFERENCED_COLUMN_NAME = '<column>';
Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.
Solution 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.
Solution 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';
Solution 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,
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>';
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();
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;
Solution 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.
Christian Oudard
My programming interests include web development, databases, algorithms, AI, and optimization. My favorite language is Python.
Updated on April 16, 2022Comments
-
Christian Oudard about 2 years
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.
-
Christian Oudard over 15 yearsactually, that points the wrong direction. that query shows all the foreign keys pointing FROM 'mytable', not all foreign keys pointing TO 'mytable'.
-
moriesta almost 12 yearsthis always gives me an empty set, while query proposed by Node below works fine
-
Vinko Vrsalovic almost 12 years@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 '>'?
-
moriesta almost 12 yearsSeems like I misunderstood you query, because I was querying for keys referencing FROM the <table> :) (yes, I wrote table name instead of "<table>" XD)
-
Kohányi Róbert almost 12 yearsThis 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.
-
guigouz over 11 yearsThose 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/…
-
ChrisV over 10 yearsYou can get foreign keys in both directions from the REFERENTIAL_CONSTRAINTS table – I have added another answer with the query.
-
Barmar about 10 yearsThis shows all the constraints in
<yourtable>
, not all the constraints that point to<yourtable>
. -
user12345 almost 10 yearsUnless 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'
-
Mark Amery over 9 yearsAs @Barmar 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.
-
Luke Madhanga over 9 yearsI personally prefer this answer as using the REFERENTIAL_CONSTRAINTS table gives you the update and cascade rule. +1
-
Deepak Ram over 9 yearsThis wont work in case of non root user even though the user have all permissions to the database
-
A_user over 9 yearsDoing discovery about a table you shouldn't forget that foreign keys can be established BOTH ways!
-
Beau Bouchard about 9 yearsgreat answer. Do you have any solutions for MyISAM?
-
Andy about 9 yearsMyISAM does not support foreign keys, unfortunately. dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html
-
Jigar almost 9 years@MarkAmery : this is first result of
display foreign keys mysql
in google, may be that is why ;) -
william.eyidi over 8 yearsthisi 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
-
Charles Wood over 8 yearsThis 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!
-
Matthew Read almost 7 years@DeepakRam This works perfectly for me with a privileged user that is not the root user.
-
omarjebari over 6 yearsAgreed, i only ever get an empty set here! Andy's answer worked best for me.
-
Lealo over 6 yearsWhy is it required so much? Why not make it as easy as how you select rows?
-
wheelerswebservices about 6 yearsThis worked perfect for me. I have most privileges, but not root. You're a life saver.
-
Arvid over 5 yearsI 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. -
DimiDak about 5 yearsI love this kind of answers sir.
-
Andreas almost 5 yearsThanks great solution! I needed the column name as well, to get it add: 'k.COLUMN_NAME'
-
DJDave almost 4 yearsI 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
-
SherylHohman over 3 yearsPlease 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.
-
AnrDaemon about 2 yearsKnowing names of the foreign keys doesn't really help in any meaningful way, shape or form.
-
user110857 almost 2 yearsThis 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.