วิธีดู Foreign Keys ทั้งหมดที่อ้างอิงถึงตารางหรือคอลัมน์ใน MySQL

ใช้ view INFORMATION_SCHEMA.KEY_COLUMN_USAGE เพื่อดู Foreign Key constraints

สร้างตารางตัวอย่าง 2 ตาราง และสร้าง FK:

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)
mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references primaryTable1(Fk_pk); Query OK, 0 rows affected (1.57 sec)

ดู FK ทั้งหมดที่อ้างอิงถึงตาราง (Table-level)

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'yourReferencedTableName';

ตัวอย่าง query:

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

ดู FK ที่อ้างอิงถึงคอลัมน์เฉพาะ (Column-level)

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';

ตัวอย่าง query:

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