How do I see all foreign keys to a table or column?

转载于 2019-07-02 22:50:38 发布

· 328 阅读

1 ·

版权

原文链接：http://www.cnblogs.com/kungfupanda/p/5935368.html

本文提供了一种使用SQL查询特定数据库中所有外键关系的方法，包括表名、列名、约束名以及引用的表和列名。通过修改查询条件，可以针对特定的表或列进行外键查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

down vote accepted

For a 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>' AND REFERENCED_TABLE_NAME = '<table>';

For a 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>' 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_USAGE where TABLE_SCHEMA = "<your_database_name>" and TABLE_NAME = "<your_table_name>" and referenced_column_name is not NULL;