在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天24分享网就整理分享《如何查看表列的所有外键？》，聊聊，希望可以帮助到正在努力赚钱的你。

要查看表或列的所有外键，referenced_column_name 命令是

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

创建第一个表 -

mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows affected (0.43 sec)

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

mysql> CREATE table primaryTable1 -> ( -> Fk_pk int, -> DeptName varchar(200), -> Primary key(Fk_pk) -> ); Query OK, 0 rows affected (0.48 sec)

现在，两个表都在 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); Query OK, 0 rows affected (1.57 sec) 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)

理论要掌握，实操不能落！以上关于《如何查看表列的所有外键？》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注the24.cn吧！