要查看表或列的所有外键，referenced_column_name 命令是 首先，创建两个表，然后在外键约束的帮助下关联起来。创建第一个表 -Mysql> CREATE table ForeignTable -> ( ->
要查看表或列的所有外键，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)--结束END--
本文标题: 如何查看表列的所有外键？
本文链接: https://www.lsjlt.com/news/436921.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-04-26
2024-04-26
2024-04-26
2024-04-26
2024-04-26
2024-04-26
2024-04-24
2024-04-24
2024-04-24
2024-04-24
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0