你的位置:首页 > 数据库

[数据库]How do I see all foreign keys to a table or column?


 

down voteaccepted

For a Table:

SELECT  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_SCHEMA = '<database>' AND REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_SCHEMA = '<database>' AND REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.


 

use INFORMATION_SCHEMA;select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGEwhere TABLE_SCHEMA = "<your_database_name>" and TABLE_NAME = "<your_table_name>" and referenced_column_name is not NULL;