How do I see all foreign keys to a table column?

To see all the foreign keys to a table or column, the referenced_column_name command is used.

First, two tables are created and then related with the help of the foreign key constraint.

Creating the first table −

mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );
Query OK, 0 rows affected (0.43 sec)

After creating the first table successfully, the second table is created as follows −

mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );
Query OK, 0 rows affected (0.48 sec)

Now, both the tables are related with the help of the alter command and the foreign key constraint is also added. The syntax for this is as follows −

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

The above syntax is applied to relate both the tables as follows −

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

Now, the syntax to see all the foreign keys to a table is given as follows −

For a table −

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

Now the above syntax is used to create the query to view all the foreign keys. The query is given as follows −

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

The following is the output −

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

In the sample output, the constraint_name is ‘constFKPK’ and table_name is ‘foreigntable’. For a column −

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

The query to display all the foreign keys to a column is given using the above syntax. The query is as follows −

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

The output obtained is as follows:

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