Get a list of Foreign Key constraints in MySQL
Let’s say we have a database “business” with number of tables. If you want to show only foreign key constraints, then use the following query −
mysql> select *
−> from information_schema.referential_constraints
−> where constraint_schema = 'business';
The following is the output displaying only foreign key constraints −
+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+
| def | business | ConstChild | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | childdemo | parentdemo |
| def | business | ConstFK | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | tblf | tblp |
| def | business | constFKPK | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | foreigntable | primarytable1 |
| def | business | FKConst | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | foreigntabledemo | primarytabledemo |
| def | business | primarytable1demo_ibfk_1 | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | primarytable1demo| foreigntable1 |
| def | business | StudCollegeConst | def | business | PRIMARY | NONE | NO ACTION | NO ACTION | studentenrollment| college |
+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+
6 rows in set (0.07 sec)
Published on 11-Jan-2019 12:14:24
