Key Cookbooks

Adding foreign key to an existing table Changing the primary key Referencing column and referenced column are incompatible Searching all foreign keys to a table or column Temporarily disabling a foreign key constraint

thumb_up

thumb_down

chat_bubble_outline
0
 auto_stories new 
settings

Searching all foreign keys to a table or column in MySQL

Database

MySQL

Cookbooks

Tables Cookbooks

Key Cookbooks

Mar 9, 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.