How to List All Foreign Keys to Table in MySQL

MySQL allows you to build complex relationships between database tables using foreign key constraints whereby one or more columns of a table refer to identical columns in another table, implying a relationship between those tables. Sometimes database developers need to view all foreign keys to table or even a column in MySQL. In this article, we will learn how to list all foreign keys to table in MySQL.

How to List All Foreign Keys to Table in MySQL

Information_schema is a system generated database created at the time of MySQL installation. It holds information about all tables, columns and even all foreign keys in each database on your server. This information is automatically updated as and when new foreign keys are created, existing ones are modified or deleted. In this database, the table KEY_COLUM_USAGE specifically contains information about foreign keys in your databases.

You can use the following SQL query to get a list of all foreign keys to table. Replace database_name and table_name with the names of your database and table respectively. Also make sure you have proper privileges to run this query.

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

It will list table name, column name, foreign key constraint name, its reference table and reference column. If you want information about multiple tables table1, table2, table3, etc. you can use IN clause in above query, for REFERENCED_TABLE_NAME.

REFERENCED_TABLE_NAME IN ('table1','table2','table3');

Similarly, if you want to view all foreign keys to a given column, modify the above query as shown below.

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'database_name' AND
  REFERENCED_TABLE_NAME = 'table_name' AND
  REFERENCED_COLUMN_NAME = 'column_name';

If you want information about multiple columns, you can use IN clause for REFERENCED_COLUMN_NAME above.

REFERENCED_COLUMN_NAME IN ('column1','column2','column3');

In this article, we have learnt how to list all foreign keys to table as well as columns in MySQL. You can customize it as per your requirement.

Also read:

How to Check if Row Exists in MySQL
How to Center Div using jQuery
How to Select Element by Data Attribute in jQuery
How to Find When Table Was Last Updated in MySQL
How to Truncate All Tables in Database in MySQL