How to Find the Name of a Constraint in MySQL

Problem:

You want to find the names of the constraints in a table in MySQL.

Example:

We want to display the names of the constraints in the table student.

Solution:

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE table_name='student';

Here is the result:

Discussion:

Use the view table_constraints in the information_schema schema. This view contains a lot of columns, but the most important are table_name, constraint_type, and constraint_name. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint. The column constraint_type indicates the type of constraint:

• PRIMARY KEY for the primary key type,
• FOREIGN KEY for the foreign key type,
• UNIQUE for the unique values,
• CHECK for the constraint check.

In our example, you can see the constraint named PRIMARY for the primary key in the student table. The constraint_type column gives you information about the type of each constraint; for the primary key, it is PRIMARY KEY.