JIYIK CN >

In this tutorial, we aim to explore how to display foreign keys for tables and columns in MySQL.

The type of key that references a primary key, also known as the primary key of another table, is called a foreign key. Understanding the foreign keys of a table is important when working with MySQL.

Furthermore, columns of a particular table can also have foreign keys associated with it. Let us try to understand how to get these foreign keys.

Creating a Table in MySQL

Before we begin, we will create a dummy dataset to work with. Here, we will create a table student_detailsand 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 containing the first and last names of students. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code will 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

Displaying foreign keys of a table in MySQL

To get the foreign keys of a table in MySQL, we use the following code block:

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>';

We can see that in the query mentioned above, we need to enter the database and table name to get the foreign keys. This task can be accomplished 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>';

As mentioned earlier, the database name for the above query is boatdband the table name is student_details. The output of the above 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

Additionally, we can find the foreign keys associated with a specific 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 extra is added REFERENCED_COLUMN_NAME.

So, with the help of this technique, we can effectively display the foreign keys associated with a particular table and column.