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

问题描述

我有一个表的主键在其他几个表中被引用为外键。例如：
$ b $ pre $ code $ CREATE TABLE`X`（
`X_id` int NOT NULL auto_increment，
` （````）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 ``）参考``X`（`X_id`）
）
CREATE TABLE`Z`（
`Z_id` int（11）NOT NULL auto_increment，$ b $```varchar 255）NOT NULL，
`X_id` int DEFAULT NULL，
PRIMARY KEY（`Z_id`），
CONSTRAINT`Z_X` FOREIGN KEY（`X_id`）REFERENCES`X`（`X_id `）
）

现在，我不知道有多少个表包含外键的数据库，如表Y和Z.是否有一个SQL查询，我可以用来返回：

1. 具有外键的表


2. AND WHIC这些表中的h实际上具有外键值

解决方案

p>

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

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

$ $ $ $ $
$ b $ KEY_COLUMN_USAGE $ b $ WHERE
REFERENCED_TABLE_NAME = 'X'
AND REFERENCED_COLUMN_NAME ='X_id'
AND TABLE_SCHEMA ='your_database_name';

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`)
  )

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. A list of tables that have foreign keys into X
2. AND which of those tables actually have values in the foreign key

解决方案

Here you go:

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屋！