How to Find the Name of a Constraint in MySQL

Database:

MySQL

Operators:

CONSTRAINT
PRIMARY KEY
FOREIGN KEY
UNIQUE
DEFAULT
CHECK

Table of Contents

Problem
Example
Solution
Discussion

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_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
student	PRIMARY KEY	PRIMARY
student	UNIQUE	personal_number
student	FOREIGN KEY	student_ibfk_1
student	CHECK	student_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.

Problem

Example

Solution

Discussion

Recommended courses:

Recommended articles:

See also: