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

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

584189 次浏览
小开

如果您使用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';
小开

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

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

然后，在贝壳里，

grep 'refs_tablename_id' mysql_output
小开
最佳答案

一张桌子：

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

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

小开

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

我遇到了类似的问题，但我还想看到CONSTRAINT_TYPE以及REFERENCED表和列名。

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

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.

To check:

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

要修复，请使用此操作：

ALTER TABLE `<yourtable>` ENGINE=InnoDB;
小开

编辑：正如评论中指出的，这不是OPs问题的正确答案，但知道这个命令很有用。这个问题出现在Google中，我正在寻找什么，并想我会留下这个答案让其他人找到。

SHOW CREATE TABLE `<yourtable>`;

我在这里找到了答案： MySQL：显示表命令的约束

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

小开

一个快速的方法来列出你的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;

此查询确实假设约束以及所有引用和引用的表都在同一个架构中。

添加您自己的评论。

来源：官方mysql手册。

小开

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

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;

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

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

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

table_name='table_name

在查询的末尾。

受这篇文章的启发这里

小开

作为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>'

对于

中的外键，或

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

用于

的外键

如果你想要的话，你也可以得到UPDATE_RULE和DELETE_RULE。

小开

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

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

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

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

如果还想获取外键列的名称：

SELECT i.TABLE_SCHEMA, i.TABLE_NAME,
i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME,
k.COLUMN_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.TABLE_SCHEMA = '<TABLE_NAME>' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY i.TABLE_NAME;
小开

我不愿意再添加另一个答案，但是我不得不乞求、借用和窃取其他人来得到我想要的，即给定模式中表上所有FK关系的完整列表，包括与其他模式中表的FK。两个关键的记录集information_schema.KEY_COLUMN_USAGE和information_schema.referential_constraints。如果缺少您想要的属性，只需取消注释KCU。，RC。查看可用的

SELECT DISTINCT KCU.TABLE_NAME, KCU.COLUMN_NAME, REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, UPDATE_RULE, DELETE_RULE #, KCU.*, RC.*
FROM information_schema.KEY_COLUMN_USAGE KCU
INNER JOIN information_schema.referential_constraints RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE TABLE_SCHEMA = (your schema name)
AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY KCU.TABLE_NAME, KCU.COLUMN_NAME;
小开

SQL中的约束是为表中的数据定义的规则。约束还限制进入表的数据类型。如果新数据不遵守这些规则，则操作将中止。

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY';

您可以使用select * from information_schema.table_constraints;查看所有约束

（这将产生大量的表数据）。

您也可以将其用于MySQL：

show create table tableName;
小开

我需要对表之间的关系进行鸟瞰（以便在ORM中使用）。使用此页面中的建议，经过实验，我整理了以下查询：

SELECT
KCU.CONSTRAINT_NAME,
KCU.TABLE_NAME,
KCU.COLUMN_NAME,
KCU.REFERENCED_TABLE_NAME,
KCU.REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
ON
COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND COLS.TABLE_NAME   = KCU.TABLE_NAME
AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
KCU.TABLE_NAME,
COLS.ORDINAL_POSITION

它只返回我需要的东西，并且按照我想要的顺序。

我还对结果进行了少量处理（将其转换为某种字典），以便它可以用于创建聚合。

小开

了解更新和删除行为通常很有帮助，而其他答案则不提供。所以就这样吧。

SELECT cu.table_name,
cu.column_name,
cu.constraint_name,
cu.referenced_table_name,
cu.referenced_column_name,
IF(rc.update_rule = 'NO ACTION', 'RESTRICT', rc.update_rule) AS update_rule,-- See: https://stackoverflow.com/a/1498015/2742117
IF(rc.delete_rule = 'NO ACTION', 'RESTRICT', rc.delete_rule) AS delete_rule -- See: https://stackoverflow.com/a/1498015/2742117
FROM information_schema.key_column_usage cu
INNER JOIN information_schema.referential_constraints rc ON rc.constraint_schema = cu.table_schema
AND rc.table_name = cu.table_name
AND rc.constraint_name = cu.constraint_name
WHERE cu.referenced_table_schema = '<your schema>'
AND cu.referenced_table_name = '<your table>';
小开

我有一个“myprodb”MySql数据库，为了检查这个数据库中的所有外键，我使用了以下简单的命令。

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'myprodb' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

希望有帮助。


提交答案