Back to cookbooks list Articles Cookbook

How to Find the Name of a Constraint in MySQL

  • CONSTRAINT
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • DEFAULT
  • CHECK

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:

TABLE_NAMECONSTRAINT_TYPECONSTRAINT_NAME
studentPRIMARY KEYPRIMARY
studentUNIQUEpersonal_number
studentFOREIGN KEYstudent_ibfk_1
studentCHECKstudent_chk_1

Discussion:

Discover the best interactive MySQL courses

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.

Recommended courses:

Recommended articles:

See also: