How to view all foreign keys of a table column?

To view all foreign keys of a table or column, the referenced_column_name command is

First, create two tables and then associate them with the help of foreign key constraints.

Create the first table-

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

After successfully creating the first table, create the second table 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 tables are in alter command associated with the help of foreign keys Constraints were also added. The syntax 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 used to associate two 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 view all foreign keys of a table is 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 create a query using the above syntax to view all foreign keys. 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_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 example output, 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';

gives a query showing all foreign keys of the column using the above syntax. The query is as follows 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)

The above is the detailed content of How to view all foreign keys of a table column?. For more information, please follow other related articles on the PHP Chinese website!