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, and 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.