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.
This question is related to
mysql
foreign-keys
innodb
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;
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;
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.
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';
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>';
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';
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;
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
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';
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>';
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;
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;
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.
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';
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;
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
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.
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
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.
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.
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.
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;
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;
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
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';
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
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
Source: Stackoverflow.com