How do I show unique constraints of a table in MySQL?

You can show unique constraints of a table in MySQL using information_schema.table_constraints.

The syntax is as follows.

SELECT DISTINCT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
TABLE_NAME = ’yourTableName’ AND CONSTRAINT_TYPE = ’UNIQUE’;

To understand the above syntax, let us create a table with UNIQUE constraint −

mysql> create table UniqueConstraint
   -> (
   -> Id int,
   -> FirstName varchar(30),
   -> LastName varchar(30),
   -> constraint uniqueFirstNameAndLastName UNIQUE(FirstName,LastName)
   -> );
Query OK, 0 rows affected (0.74 sec)

Implement the above syntax to display the name of the constraint which is a unique constraint from a MySQL table. The query is as follows −

mysql> select distinct CONSTRAINT_NAME
   -> from information_schema.TABLE_CONSTRAINTS
   -> where table_name = 'UniqueConstraint' and constraint_type = 'UNIQUE';

The following is the output −

| CONSTRAINT_NAME            |
+----------------------------+
| uniqueFirstNameAndLastName |
+----------------------------+
1 row in set, 2 warnings (0.01 sec)

If you want to display all the constraints of a MySQL table, use the following query.

mysql> SELECT DISTINCT CONSTRAINT_NAME
   -> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   -> WHERE CONSTRAINT_SCHEMA = 'MySQL'\G

The following is the output −

*************************** 1. row ***************************
CONSTRAINT_NAME − PRIMARY
*************************** 2. row ***************************
CONSTRAINT_NAME − name
2 rows in set, 2 warnings (0.01 sec)