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)

Vrundesha Joshi

Published on 11-Jan-2019 07:44:24

• Related Questions & Answers
• Get a list of Constraints from MySQL Database?
• sp_help for MySQL to display field types and foreign key constraints?
• How to use Primary Key Constraints and Foreign Key Constraints to enforce database integrity in Oracle?
• How can we add FOREIGN KEY constraints to more than one fields of a MySQL table?
• MySQL Syntax to create Foreign Key?
• Foreign Key in RDBMS
• How to identify foreign key in MySQL DB?
• Foreign key referencing of two DB2 tables
• Difference between Primary key and Foreign key in Database
• Difference Between Primary key and Foreign key in DBMS
• Difference between Primary key and Foreign key in SQL Database
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
• Basics of Foreign Keys in MySQL?
• MySQL error 1452 - Cannot add or a child row: a foreign key constraint fails
• How to get primary key of a table in MySQL?