我有一个表，它的主键在其他几个表中作为外键引用。例如:
CREATE TABLE `X` (
`X_id` int NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`X_id`)
)
CREATE TABLE `Y` (
`Y_id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`X_id` int DEFAULT NULL,
PRIMARY KEY (`Y_id`),
CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)
CREATE TABLE `Z` (
`Z_id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`X_id` int DEFAULT NULL,
PRIMARY KEY (`Z_id`),
CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)
现在，我不知道数据库中有多少表包含X的外键，如表Y和z。是否有一个SQL查询，我可以使用返回:
具有X外键的表列表
AND这些表中哪些外键中有值
06 2022
基于@Panayotis的回答，但结构更好。
这将从多个表中列出所有约束。
我还包含了TABLE_SCHEMA以显示数据库名称。
SQL
SELECT
TABLE_SCHEMA AS 'Database',
TABLE_NAME AS t1,
REFERENCED_TABLE_NAME AS 't2 (reference table)',
COLUMN_NAME AS 't1 column',
REFERENCED_COLUMN_NAME AS 't2 column (reference table)',
CONSTRAINT_NAME AS 't1 (constrain name)'
FROM
information_schema.key_column_usage
WHERE
referenced_table_name IS NOT NULL
输出
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| Database | t1 | t2 (reference table) | t1 column | t2 column (reference table) | t1 (constrain name) |
+============+============+=====================+===============+============================+========================+
| foobar | credential | userdetail | userdetail_fk | id | credentialUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| foobar | loginlog | userdetail | userdetail_fk | id | loginlogUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
+ client | userdetail | client | client_fk | id | userdetailClientFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
MySQL 5.5参考手册:InnoDB和外键约束
SELECT
ke.REFERENCED_TABLE_SCHEMA parentSchema,
ke.referenced_table_name parentTable,
ke.REFERENCED_COLUMN_NAME parentColumnName,
ke.TABLE_SCHEMA ChildSchema,
ke.table_name childTable,
ke.COLUMN_NAME ChildColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
AND ke.REFERENCED_COLUMN_NAME = 'ci_id' ## Find Foreign Keys linked to this Primary Key
ORDER BY
ke.referenced_table_name;
这个解决方案不仅会显示所有的关系，还会显示约束的名称，这在某些情况下是必需的(例如drop constraint):
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';
06 2022
基于@Panayotis的回答，但结构更好。
这将从多个表中列出所有约束。
我还包含了TABLE_SCHEMA以显示数据库名称。
SQL
SELECT
TABLE_SCHEMA AS 'Database',
TABLE_NAME AS t1,
REFERENCED_TABLE_NAME AS 't2 (reference table)',
COLUMN_NAME AS 't1 column',
REFERENCED_COLUMN_NAME AS 't2 column (reference table)',
CONSTRAINT_NAME AS 't1 (constrain name)'
FROM
information_schema.key_column_usage
WHERE
referenced_table_name IS NOT NULL
输出
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| Database | t1 | t2 (reference table) | t1 column | t2 column (reference table) | t1 (constrain name) |
+============+============+=====================+===============+============================+========================+
| foobar | credential | userdetail | userdetail_fk | id | credentialUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| foobar | loginlog | userdetail | userdetail_fk | id | loginlogUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
+ client | userdetail | client | client_fk | id | userdetailClientFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
