如何查看表列的所有外键？

2023-06-21 03:44:03 66

要查看表或列的所有外键，请使用referenced_column_name命令。

首先，创建两个表，然后在外键约束的帮助下关联。

创建第一个表-

mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );

成功创建第一个表后，创建第二个表如下-

mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );

现在，这两个表都在alter命令的帮助下相关联，并且还添加了外键约束。语法如下-

alter table yourFirstTable add constraint anyConstraintName foreign key(column_name which is
acts foreign key in second table) yourSecondTable(column_name which acts primary key in
second table).

上述语法适用于将两个表关联如下-

mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references
primaryTable1(Fk_pk);
Records: 0 Duplicates: 0 Warnings: 0

现在，查看表的所有外键的语法如下：

对于表-

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'yourReferencedTableName';

现在上面的语法用于创建查询以查看所有外键。查询如下-

mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
-> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE REFERENCED_TABLE_NAME = 'primarytable1';

以下是输出-

+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk       | constFKPK       | primarytable1         | fk_pk                  |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.02 sec)

在示例输出中，constraint_name是“constFKPK”，table_name是“foreigntable”。对于列-

SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'yourDatabaseName' AND
REFERENCED_TABLE_NAME = 'yourreferencedtablename' AND
REFERENCED_COLUMN_NAME = 'yourreferencedcolumnname';

使用上述语法给出显示列的所有外键的查询。查询如下-

mysql> SELECT
-> TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
-> FROM
-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE
-> REFERENCED_TABLE_SCHEMA = 'business' AND
-> REFERENCED_TABLE_NAME = 'primarytable1' AND REFERENCED_COLUMN_NAME
= 'fk_pk';

得到的输出如下：

+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME |REFERENCED_COLUMN_NAME  |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk       | constFKPK       | primarytable1         | fk_pk                  |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)

如何查看表列的所有外键？

热门推荐

随机推荐