In this article, we provide you with several methods that you can use to list all foreign keys for all tables in the MySQL database.
1. Using SQL Query
Let’s begin with the first way, the SQL query. So, to show all foreign keys for all tables in the MySQL database. We will use the following script:
SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name
FROM information_schema.referential_constraints RefCons
JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema
AND RefCons.table_name = KeyCol.table_name
AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'DATABASE_NAME';Sample result
The output of the SQL query:
The above SQL script listed all foreign keys in the database, but what if you want to list all foreign keys for a specific table in the MySQL database? For that you can use the following SQL query:
SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name
FROM information_schema.referential_constraints RefCons
JOIN information_schema.key_column_usage KeyCol
ON RefCons.constraint_schema = KeyCol.table_schema
AND RefCons.table_name = KeyCol.table_name
AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'DATABASE_NAME'
AND RefCons.Table_name ='TABLE_NAME';
The output of the query for the Customer table:
2. Using MySQL interactive shell command-line
Another method that you can use to list all foreign keys for a specific table in a MySQL database will be with the use of the SHOW CREATE TABLE command in the MySQL command line as follow:
SHOW CREATE TABLE Database_name.Table_name;
Result for the customer table:
3. list all primary keys in MySQL using ERBuilder Data Modeler
On top of the two above methods, there is an ultimate way to help you list all foreign keys for a table in your MySQL database. With ERBuilder Data Modeler you can visualize your database structures from schema, tables and all their details (columns, constraints, indexes, triggers, keys ..), relationships, and more.
With ERBuilder Data Modeler you can list all foreign keys for a table in your MySQL database, just by simply double-clicking on the table after it clicks on columns from the menu and you will see all the foreign keys of that table as shown in the screen down below.
ERBuilder Data Modeler allows you to get all the foreign keys in your MySQL database as you can see from the screen which follows.
For more details just double-click on the name of the relationship (shown on the red rectangle in the above screenshot) and you will get the parent table, the child table, and the parent key used which is the foreign key for the child table.
Start modeling your database with us today
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
