chevron_left
Key Cookbooks
0
0
0
new
Searching all foreign keys to a table or column in MySQL
Database
chevron_rightMySQL
chevron_rightCookbooks
chevron_rightTables Cookbooks
chevron_rightKey Cookbooks
schedule Mar 9, 2022
Last updated MySQL
Tags tocTable of Contents
expand_more We can search for all foreign key constraints pointing to a particular table or a particular column in MySQL. The syntax varies slightly between searching for tables and searching for columns.
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_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE 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 residestable_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_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE 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 residestable_name
is the table within which the column you are interested in residescolumn_name
is the column you are interested in
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...