要查看表或列的所有外键，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 isacts foreign key in second table) yoursecondtable(column_name which acts primary key insecond table).
上述语法用于关联两个表，如下 -
mysql> alter table foreigntable add constraint constfkpk foreign key(fk_pk) referencesprimarytable1(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_namefrom information_schema.key_column_usagewhere 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”。对于一列 -
selecttable_name,column_name,constraint_name,referenced_table_name,referenced_column_namefrominformation_schema.key_column_usagewherereferenced_table_schema = 'yourdatabasename' andreferenced_table_name = 'yourreferencedtablename' andreferenced_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)
以上就是如何查看表列的所有外键？的详细内容。