How to Show Foreign Keys in MySQL
In this tutorial, we aim to explore how to show the foreign keys of a table and column in MySQL.
The type of keys that refer to the main key, also referred to as the primary key of another table, are called foreign keys. It is important to understand the foreign keys of a table while working with MySQL.
Moreover, a column of a particular table can also have foreign keys associated with it. Let us try to understand how to fetch these foreign keys.
Create a Table in MySQL
Before we begin, we will create a dummy dataset to work with. Here we will create a table, student_details, along with a few rows.
-- create the table student_details
CREATE TABLE student_details(
stu_id int,
stu_firstName varchar(255) DEFAULT NULL,
stu_lastName varchar(255) DEFAULT NULL,
primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName)
VALUES(1,"Preet","Sanghavi"),
(2,"Rich","John"),
(3,"Veron","Brow"),
(4,"Geo","Jos"),
(5,"Hash","Shah"),
(6,"Sachin","Parker"),
(7,"David","Miller");
The above query creates a table with rows containing the students’ first and last names. To view the entries in the data, we use the following code:
SELECT * FROM student_details;
The above code would give the following output:
stu_id stu_firstName stu_lastName
1 Preet Sanghavi
2 Rich John
3 Veron Brow
4 Geo Jos
5 Hash Shah
6 Sachin Parker
7 David Miller
Show Foreign Keys of a Table in MySQL
To fetch the foreign keys of a table in MySQL, we use the following block of code:
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>';
As we can see, in the query mentioned above, we need to enter the database and table names to fetch the foreign keys. This task can be achieved using the following query:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<boatdb>' AND
REFERENCED_TABLE_NAME = '<student_details>';
The aforementioned query has the database name as boatdb and table name as student_details as mentioned before. The output of the aforementioned code is as follows:
1,TABLE_NAME,,KEY_COLUMN_USAGE,VARCHAR,utf8mb4,64,-31,31
2,COLUMN_NAME,,KEY_COLUMN_USAGE,VARCHAR,utf8mb4,64,-31,31
3,CONSTRAINT_NAME,information_schema,KEY_COLUMN_USAGE,VARCHAR,utf8mb4,64,0,0
4,REFERENCED_TABLE_NAME,information_schema,KEY_COLUMN_USAGE,VARCHAR,utf8mb4,64,0,0
5,REFERENCED_COLUMN_NAME,information_schema,KEY_COLUMN_USAGE,VARCHAR,utf8mb4,64,0,0
Moreover, we can also find the foreign keys associated with a particular column. This can be achieved with the help of the following query:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'boatdb' AND
REFERENCED_TABLE_NAME = 'student_details' AND
REFERENCED_COLUMN_NAME = 'student_firstName';
As we can see, an additional REFERENCED_COLUMN_NAME has been added.
Therefore, with the help of this technique, we can efficiently show the foreign keys associated with a particular table and column.
