Searching all foreign keys to a table or column in MySQL

Database

chevron_right

MySQL

chevron_right

Cookbooks

chevron_right

Tables Cookbooks

chevron_right

Key Cookbooks

schedule Jul 1, 2022

Last updated

local_offer MySQL

TO a table

To check all foreign keys that are referencing a particular table:


        
        
            
                
                
                    SELECT 
  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'database_name' 
  AND REFERENCED_TABLE_NAME = 'table_name';

Where:

database_name is the database within which the table you are interested in resides
table_name is the table you are interested in

TO a column

To check all foreign keys that are linking to a particular column:


        
        
            
                
                
                    SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'database_name' AND
  REFERENCED_TABLE_NAME = 'table_name' AND 
  REFERENCED_COLUMN_NAME = 'column_name';

Where:

database_name is the database within which the table you are interested in resides
table_name is the table within which the column you are interested in resides
column_name is the column you are interested in

MySQL | Foreign keys

A foreign key in MySQL refers to a column (or a group of columns) in one table that refers to the primary key in another table. The table containing the foreign key is referred to as the child table, while the table containing the primary key being referred to is called the parent table.

chevron_right

mail

Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)