查找包含特定外键(如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 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>';

方法列出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官方手册。

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

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

你可以使用select * from information_schema.table_constraints;

(这会产生大量的表数据)。

你也可以在MySQL中使用:

show create table tableName;

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

我遇到了类似的问题，但我还想查看CONSTRAINT_TYPE以及引用的表和列名。所以,

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

记住!

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

检查:

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

要修复，使用这个:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;