How to display all constraints on a table in MySQL?

To display all constraints on a table, you can try any of the following methods −

Method 1 − Using SHOW command

You can check with the help of show command. The syntax is as follows −

SHOW CREATE TABLE yourTableName;

Method 2 − Using information.schema

You can use information.schema. The syntax is as follows −

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'yourTableName';

To display all constraints on a table, implement the above syntax. Let’s say we already have a table ‘ConstraintDemo’.

The query is as follows −

mysql> select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
   −> from information_schema.KEY_COLUMN_USAGE
   −> where TABLE_NAME = 'ConstraintDemo';

The following is the output displaying the constraints −

+-------------+-----------------+------------------------+-----------------------+
| COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_COLUMN_NAME | REFERENCED_TABLE_NAME |
+-------------+-----------------+------------------------+-----------------------+
| Id          | PRIMARY         | NULL                   |                  NULL |
| Id          | Id              | NULL                   |                  NULL |
+-------------+-----------------+------------------------+-----------------------+
2 rows in set, 2 warnings (0.04 sec)

Now let us check using the show command. The query is as follows −

mysql> show create table ConstraintDemo;

The following is the output −

+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table          |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ConstraintDemo | CREATE TABLE `constraintdemo` (`Id` int(11) NOT NULL,`Name` varchar(100) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Id` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)