Back to cookbooks list Articles Cookbook

What Is the Default Constraint Name in MySQL?

Problem:

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

Example:

In our database, we create two tables, country and student, with the following constraints: PRIMARY KEY (the column

id
in the tables country and student), FOREIGN KEY (the column country_id in the table student), DEFAULT (the column name in the table student), UNIQUE (the column name in the table country and the column personal_number in the table student), and CHECK (the column age in the table student).

Solution:

CREATE TABLE country (
id int NOT NULL primary key,
name varchar(100) UNIQUE
);

CREATE TABLE student (
id int NOT NULL primary key,
name varchar(100) NOT NULL DEFAULT 'unknown',
personal_number varchar(100) UNIQUE,
age int CHECK(age>15),
country_id int,
FOREIGN KEY(country_id) REFERENCES country(id)
);

Let’s display how MySQL names the constraints of these tables by default. Use this query to see them:

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

Here is the result:

TABLE_NAMECONSTRAINT_TYPECONSTRAINT_NAME
countryPRIMARY KEYPRIMARY
countryUNIQUEname
studentPRIMARY KEYPRIMARY
studentUNIQUEpersonal_number
studentFOREIGN KEYstudent_ibfk_1
studentCHECKstudent_chk_1

Discussion:

When we create our tables, we set constraints for some columns. However, we don’t specify the names for these constraints, so they are named by default in MySQL. In MySQL, the default constraint types are PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK. How are the default names generated for constraints?

The PRIMARY KEY constraint is simply named PRIMARY, because there is always one primary key in a table. There are two constraints named PRIMARY in the result above, but the tables to which they belong are different.

For FOREIGN KEY, the name follows the convention: the table name, an underscore (‘_’), ‘ibfk’, another underscore (‘_’), and a number. For our example, it is student_ibfk_1 in the table student.

The default name for a CHECK constraint is similar to that of the foreign key. It starts with the table name, an underscore (‘_’), ‘chk’, another underscore (‘_’), and a number. For our example, it is student_chk_1 in the table student.

The default name of a UNIQUE constraint is the name of the column itself. For our example, it is personal_number in the table student. When there are multiple columns with the UNIQUE constraint, it uses the name of the first column in the definition of the constraint.

Recommended courses:

Recommended articles:

See also: