How to See Foreign Keys Related to Table or Column

MySQL is a popular relational database used by many organizations. The relations in a database are commonly established using foreign keys. Foreign keys are one or more columns that reference another table. Database programmers and administrators often need to find out which foreign keys point to a given table or column. In this article, we will learn how to get foreign keys related to table or column.

What is Foreign Key

Foreign key is a collection of one or more columns from one table that reference columns from another table. The table with foreign key is the child table and the referenced table is the parent table. It creates a referential integrity between two tables. In the absence of foreign keys, it would be really difficult to understand how the data in two tables are related.

Why you need to see foreign keys related to table

Before you create a foreign key to a table it is important to find out if there are already any foreign keys pointing to it. This will help you avoid duplicate or redundant keys. It will also help you understand other foreign key constraints that are already applicable to your table, before you add a new foreign key to it. This is important especially when you delete rows from the table.

How to See Foreign Keys Related to Table or Column

There are several ways to get a list of foreign keys related table or column in MySQL. Let us say you have two tables orders and products, with the products table containing a foreign key pointing to orders table.

create table products(
  id int,
  product_name varchar(255),
  primary key (id)  
);

create table orders (
    order_id int,
    product_id int,
    order_date date,
    primary key (order_id),
    foreign key (product_id) references products(id)
);

Please ensure that products table is created before orders table, since orders table contains a foreign key pointing to products table.

1. Foreign Keys to Table

MySQL and SQL Server database contain information_schema database that contains meta information about all databases and tables in the server. You can use the following query to get a list of foreign keys pointing to a table. Replace <table_name> with the name of the table for which you want to determine the list of foreign keys.

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table_name>' \G

For example, here is the query to get foreign keys to products table.

mysql> SELECT
       TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
       FROM
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE
       REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
       REFERENCED_TABLE_NAME = 'products' \G
*************************** 1. row ***************************
            TABLE_NAME: orders
           COLUMN_NAME: product_id
       CONSTRAINT_NAME: orders_ibfk_1
 REFERENCED_TABLE_NAME: products
REFERENCED_COLUMN_NAME: id

The above result displays the table referencing products (that is, orders), its foreign key column name, constraint_name and the referenced parent table products.

2. Foreign Key to Column

If you want to view the foreign keys pointing to a column of your table, you can 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 = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table_name>' AND
  REFERENCED_COLUMN_NAME = '<column_name>' \G

Here is a query to get foreign keys pointing to ID column of products table.

mysql>   SELECT
       TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
       FROM
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE
       REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
       REFERENCED_TABLE_NAME = 'products' AND
       REFERENCED_COLUMN_NAME = 'id' \G
*************************** 1. row ***************************
            TABLE_NAME: orders
           COLUMN_NAME: product_id
       CONSTRAINT_NAME: orders_ibfk_1
 REFERENCED_TABLE_NAME: products
REFERENCED_COLUMN_NAME: id

Conclusion

In this article, we have learnt how to see foreign keys pointing to table or column. You can use any of these methods depending on your requirement. It is important to keep an eye on foreign keys referencing your table or column to avoid creating duplicate constraints and also for considering them at the time of deletion.

Also read:

How to Reset Auto Increment in MySQL
How to List Users in MySQL
How to Get Character Set of MySQL Database