如何查找所有具有引用特定 table.column 的外键并具有这些外键值的表? [英] How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?

问题描述

我有一个表，其主键在其他几个表中被引用为外键.例如:

I have a table whose primary key is referenced in several other tables as a foreign key. For example:

  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 查询:

Now, I don't know how many tables there are in the database that contain foreign keys into X like tables Y and Z. Is there a SQL query that I can use to return:

1. 具有 X 外键的表的列表
2. AND 哪些表实际上在外键中有值

推荐答案

给你:

USE information_schema;
SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id';

如果您有多个具有相似表/列名称的数据库，您可能还希望将查询限制为特定数据库:

If you have multiple databases with similar tables/column names you may also wish to limit your query to a particular database:

SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id'
  AND TABLE_SCHEMA = 'your_database_name';

这篇关于如何查找所有具有引用特定 table.column 的外键并具有这些外键值的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！