MySQL 如何查找所有具有引用特定 table.column 的外键并具有这些外键值的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/806989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
提问by Alex N.
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:
现在，我不知道数据库中有多少表包含 X 中的外键，如表 Y 和 Z。是否有我可以使用的 SQL 查询返回：
- A list of tables that have foreign keys into X
- AND which of those tables actually have values in the foreign key
- 具有 X 外键的表列表
- AND 哪些表实际上在外键中有值
回答by Alex N.
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';
回答by Ovidiu
MySQL 5.5 Reference Manual: "InnoDB and FOREIGN KEY Constraints"
MySQL 5.5 参考手册：“InnoDB 和 FOREIGN KEY 约束”
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;
回答by Panayotis
This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. 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;
If you want to check tables in a specific database, add the following:
如果要检查特定数据库中的表，请添加以下内容：
AND table_schema = 'database_name';
回答by Seb
You can find all schema related information in the wisely named information_schematable.
您可以在明智命名的information_schema表中找到所有与架构相关的信息。
You might want to check the table REFERENTIAL_CONSTRAINTSand KEY_COLUMN_USAGE. The former tells you which tables are referenced by others; the latter will tell you how their fields are related.
您可能想要检查表REFERENTIAL_CONSTRAINTS和KEY_COLUMN_USAGE。前者告诉你哪些表被其他人引用；后者会告诉你他们的领域是如何相关的。
回答by bortunac
Listing all foreign keys in a db including description
列出数据库中的所有外键，包括描述
SELECT
i1.CONSTRAINT_NAME, i1.TABLE_NAME,i1.COLUMN_NAME,
i1.REFERENCED_TABLE_SCHEMA,i1.REFERENCED_TABLE_NAME, i1.REFERENCED_COLUMN_NAME,
i2.UPDATE_RULE, i2.DELETE_RULE
FROM
information_schema.KEY_COLUMN_USAGE AS i1
INNER JOIN
information_schema.REFERENTIAL_CONSTRAINTS AS i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.REFERENCED_TABLE_NAME IS NOT NULL
AND i1.TABLE_SCHEMA ='db_name';
restricting to a specific column in a table table
限制到表中的特定列
AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'
回答by jhvaras
I wrote a little bash onlinerthat you can write to a script to get a friendly output:
我写了一个小小的bash 在线工具，你可以写一个脚本来获得友好的输出：
mysql_references_to:
mysql_references_to:
mysql -uUSER -pPASS -A DB_NAME -se "USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '' AND REFERENCED_COLUMN_NAME = 'id'\G" | sed 's/^[ \t]*//;s/[ \t]*$//' |egrep "\<TABLE_NAME|\<COLUMN_NAME" |sed 's/TABLE_NAME: /./g' |sed 's/COLUMN_NAME: //g' | paste -sd "," -| tr '.' '\n' |sed 's/,$//' |sed 's/,/./'
So the execution: mysql_references_to transaccion(where transaccionis a random table name) gives an output like this:
所以执行：（mysql_references_to transaccion其中transaccion是一个随机表名）给出如下输出：
carrito_transaccion.transaccion_id
comanda_detalle.transaccion_id
comanda_detalle_devolucion.transaccion_positiva_id
comanda_detalle_devolucion.transaccion_negativa_id
comanda_transaccion.transaccion_id
cuenta_operacion.transaccion_id
...
回答by Mantas D
Easiest:
1. Open phpMyAdmin
2. On the left click database name
3. On the top right corner find "Designer" tab
最简单：
1. 打开 phpMyAdmin
2. 在左侧单击数据库名称
3. 在右上角找到“设计器”选项卡
All constraints will be shown there.
所有约束都将显示在那里。
