如何查看表列的所有外键?
mysqlmysqli database
要查看表或列的所有外键，请使用referenced_column_name命令。
首先，创建两个表，然后借助外键约束将它们关联起来。
创建第一个表 −
mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows impacted (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 constraintName foreign key(column_name which is acts foreign key in second table) yourSecondTable(column_name which act 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)
