我有一个表,它的主键在其他几个表中作为外键引用。例如:

  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外键的表列表 AND这些表中哪些外键中有值


当前回答

简单的: 1. 打开phpMyAdmin 2. 在左侧单击数据库名称 3.在右上角找到“设计者”标签

所有的约束都将显示在那里。

其他回答

简单的: 1. 打开phpMyAdmin 2. 在左侧单击数据库名称 3.在右上角找到“设计者”标签

所有的约束都将显示在那里。

这个解决方案不仅会显示所有的关系,还会显示约束的名称,这在某些情况下是必需的(例如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;

如果您想检查特定数据库中的表,请添加以下内容:

AND table_schema = 'database_name';

MySQL 5.5参考手册:InnoDB和外键约束

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;

我写了一个小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
...

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