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

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