如何查看表列的所有外鍵？-mysql教程-PHP中文網

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

WBOY

發布： 2023-09-07 20:41:07

轉載

1015 人瀏覽過

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

要查看表或列的所有外鍵，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 = &#39;yourReferencedTableName&#39;;

登入後複製

現在使用上面的語法建立查詢來查看所有外鍵。查詢如下 -

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

登入後複製

以下是輸出 -

+--------------+-------------+-----------------+-----------------------+------------------------+
| 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 = &#39;yourDatabaseName&#39; AND
REFERENCED_TABLE_NAME = &#39;yourreferencedtablename&#39; AND
REFERENCED_COLUMN_NAME = &#39;yourreferencedcolumnname&#39;;

登入後複製

使用上述語法給予顯示列的所有外鍵的查詢。查詢如下如下 -

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

登入後複製

得到的輸出如下：

+--------------+-------------+-----------------+-----------------------+------------------------+
| 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)

登入後複製

以上是如何查看表列的所有外鍵？的詳細內容。更多資訊請關注PHP中文網其他相關文章！