如何查找所有具有外键引用特定表.列并且对于这些外键具有值的表格？

Question

我有一张表，它的主键被多个其他表作为外键引用。例如：

  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查询语句可以返回以下内容：

1. 具有指向X的外键的表列表
2. 其中哪些表实际上具有外键值

- nebs

Answer 1

这是您需要的：

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

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

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

- Alex N.

---

选择TABLE_NAME比选择*更好。 - zloctb

这对哪种类型的数据库有用？MySql？ - Cirelli94

这个问题被标记为一个 MySQL 问题。 - mikeschuld

Answer 2

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;

- Ovidiu

Answer 3

这个解决方案不仅会显示所有关系，还会显示约束名，有些情况下这是必需的（例如删除约束）：

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';

- Panayotis

Answer 4

您可以在名为information_schema的表中找到所有与模式相关的信息。

您可能想要查看REFERENTIAL_CONSTRAINTS和KEY_COLUMN_USAGE表。前者告诉您哪些表被其他表引用；后者将告诉您它们的字段之间如何相关。

- Seb

Answer 5

列出数据库中所有外键，包括说明

    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'

- bortunac

Answer 6

我写了一个小的bash命令，你可以将其写入脚本中以获得友好的输出:

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
...

- jhvaras

Answer 7

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     |
+------------+------------+---------------------+---------------+----------------------------+------------------------+

- Dexter

Answer 8

从信息模式.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';

- ducky

Answer 9

最简单的方法：
1. 打开phpMyAdmin
2. 在左侧点击数据库名称
3. 在右上角找到“Designer”选项卡
所有约束都会在那里显示。

- Mantas D

---

哦，我不知道这个选项存在！ (+1)它实际上显示了所有的约束。 - Jose Manuel Abarca Rodríguez

这可能会有所帮助，但如果您有一个大型数据库，那么图表将会很混乱。 - Deepam Gupta