如何查看表或列的所有外键？ [英] How do I see all foreign keys to a table or column?

问题描述

在MySQL中，我如何获得指向特定表的所有外键约束的列表？一个特定的列？这与这个Oracle问题是一样的，但是对于MySQL。

解决方案

对于表格：

  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>'; 
  

对于栏目：

  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>'; 
  

基本上，我们在where子句中用REFERENCED_COLUMN_NAME更改了REFERENCED_TABLE_NAME。

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.

解决方案

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.

这篇关于如何查看表或列的所有外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！