我有一个表,其主键在其他几个表中作为外键引用。例如:
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 中具有外键的表的列表以及这些表中的哪些实际上在外键中具有值
干得好:
USE information_schema;
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'X'
AND REFERENCED_COLUMN_NAME = 'X_id';
如果您有多个具有相似表/列名称的数据库,您可能还希望将查询限制为特定数据库:
SELECT *
FROM
KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'X'
AND REFERENCED_COLUMN_NAME = 'X_id'
AND TABLE_SCHEMA = 'your_database_name';
MySQL 5.5 参考手册:"InnoDB and FOREIGN KEY Constraints"
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;
此解决方案不仅会显示所有关系,还会显示约束名称,这在某些情况下是必需的(例如删除约束):
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';
您可以在巧妙命名的 information_schema
表中找到所有架构相关信息。
您可能需要检查表 REFERENTIAL_CONSTRAINTS
和 KEY_COLUMN_USAGE
。前者告诉你哪些表被其他人引用;后者会告诉你他们的领域是如何相关的。
列出数据库中的所有外键,包括描述
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';
限制到表中的特定列
AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'
我写了一个小 bash onliner,您可以将其写入脚本以获得友好的输出:
mysql_references_to:
mysql -uUSER -pPASS -A DB_NAME -se "USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$1' 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/,/./'
所以执行: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
...
SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA
LIKE 'your_database' AND TABLE_SCHEMA
LIKE 'your_database' AND REFERENCED_TABLE_SCHEMA
LIKE 'your_database' AND REFERENCED_TABLE_NAME
LIKE 'your_table' AND REFERENCED_COLUMN_NAME
LIKE 'your_column';
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 |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
最简单: 1. 打开 phpMyAdmin 2. 在左侧单击数据库名称 3. 在右上角找到“设计器”选项卡
所有约束都将显示在那里。
不定期副业成功案例分享