Show Foreign Keys in MySQL

  1. Create a Table in MySQL
  2. Show Foreign Keys of a Table 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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - MySQL Key

  • Add the Primary Key to a MySQL Table
  • Turn Off Foreign Key Constraint in MySQL
  • MySQL Drop Foreign Key