MySQL 如何查看表或列的所有外键？

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

提问by Christian Oudard

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.

在 MySQL 中，如何获取指向特定表的所有外键约束的列表？特定的列？这与此 Oracle 问题相同，但适用于 MySQL。

回答by Vinko Vrsalovic

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_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

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

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

回答by CenterOrbit

EDIT: As pointed out in the comments, this is not the correct answer to the OPs question, but it is useful to know this command. This question showed up in Google for what I was looking for, and figured I'd leave this answer for the others to find.

编辑：正如评论中所指出的，这不是 OPs 问题的正确答案，但了解此命令很有用。这个问题在谷歌中出现了我正在寻找的东西，我想我会把这个答案留给其他人去寻找。

SHOW CREATE TABLE `<yourtable>`;

I found this answer here: MySQL : show constraints on tables command

我在这里找到了这个答案： MySQL : show constraints on tables 命令

I needed this way because I wanted to see how the FK functioned, rather than just see if it existed or not.

我需要这种方式是因为我想看看 FK 是如何运作的，而不仅仅是看看它是否存在。

回答by Node

If you use InnoDB and defined FK's you could query the information_schema database e.g.:

如果您使用 InnoDB 并定义了 FK，您可以查询 information_schema 数据库，例如：

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

回答by Andy

Posting on an old answer to add some useful information.

发布旧答案以添加一些有用的信息。

I had a similar problem, but I also wanted to see the CONSTRAINT_TYPE along with the REFERENCED table and column names. So,

我有一个类似的问题，但我也想看到 CONSTRAINT_TYPE 以及 REFERENCED 表和列名。所以，

1. To see all FKs in your table:

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE()
   AND i.TABLE_NAME = '<yourtable>';
   

2. To see all the tables and FKs in your schema:

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE();
   

3. To see all the FKs in your database:

   SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';
   

1. 要查看表中的所有 FK：

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE()
   AND i.TABLE_NAME = '<yourtable>';
   

2. 要查看架构中的所有表和 FK：

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE();
   

3. 要查看数据库中的所有 FK：

   SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';
   

Remember!

记住！

This is using the InnoDB storage engine. If you can't seem to get any foreign keys to show up after adding them it's probably because your tables are using MyISAM.

这是使用 InnoDB 存储引擎。如果您在添加外键后似乎无法显示任何外键，则可能是因为您的表正在使用 MyISAM。

To check:

去检查：

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

To fix, use this:

要修复，请使用：

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

回答by ChrisV

As an alternative to Node's answer, if you use InnoDB and defined FK's you could query the information_schema database e.g.:

作为 Node 答案的替代方案，如果您使用 InnoDB 并定义了 FK，则可以查询 information_schema 数据库，例如：

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND TABLE_NAME = '<table>'

for foreign keys from <table>, or

来自 <table> 的外键，或

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND REFERENCED_TABLE_NAME = '<table>'

for foreign keys to <table>

<table> 的外键

You can also get the UPDATE_RULE and DELETE_RULE if you want them.

如果需要，您还可以获取 UPDATE_RULE 和 DELETE_RULE。

回答by Panayotis

This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. drop contraint):

此解决方案不仅会显示所有关系，还会显示约束名称，这在某些情况下是必需的（例如删除约束）：

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

If you want to check tables in a specific database, at the end of the query add the schema name:

如果要检查特定数据库中的表，请在查询的末尾添加架构名称：

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'database_name';

Likewise, for a specific column name, add

同样，对于特定的列名，添加

and table_name = 'table_name

和 table_name = 'table_name

at the end of the query.

在查询结束时。

Inspired by this post here

通过这篇文章的启发这里

回答by Hazok

Using REFERENCED_TABLE_NAME does not always work and can be a NULL value. The following query can work instead:

使用 REFERENCED_TABLE_NAME 并不总是有效，并且可以是 NULL 值。以下查询可以代替：

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';

回答by Daniel Rodas

A quick way to list your FKs (Foreign Key references) using the

一种快速列出 FK（外键引用）的方法

KEY_COLUMN_USAGE view:

SELECT CONCAT( table_name, '.',
column_name, ' -> ',
referenced_table_name, '.',
referenced_column_name ) AS list_of_fks
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

This query does assume that the constraints and all referenced and referencing tables are in the same schema.

此查询确实假定约束和所有引用表和引用表都在同一架构中。

Add your own comment.

添加您自己的评论。

Source: the official mysql manual.

来源：官方mysql手册。

回答by Christian Oudard

The solution I came up with is fragile; it relies on django's naming convention for foreign keys.

我想出的解决方案很脆弱；它依赖于 Django 的外键命名约定。

USE information_schema;
tee mysql_output
SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'database_name';
notee

Then, in the shell,

然后，在外壳中，

grep 'refs_tablename_id' mysql_output

回答by Anthony Vipond

To find all tables containing a particular foreign keysuch as employee_id

查找包含特定外键的所有表，例如employee_id

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('employee_id')
AND TABLE_SCHEMA='table_name';