What Is the Default Constraint Name in PostgreSQL?

Problem:

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

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).

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)
);

Solution:

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

SELECT conname, contype
FROM pg_catalog.pg_constraint;

Here is the result:

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 PostgreSQL. How are the default names for constraints generated?

In PostgreSQL, the default constraint types are p, f, u, and c.

The PRIMARY KEY is named by default with the table name, an underscore (_), and pkey. In our result table above, you can see two PRIMARY KEY names (type p) – one by the name country_pkey in the country table and another by the name student_pkey in the student table.

A FOREIGN KEY is indicated by the constraint type f. Its name is generated by the name of the current table, an underscore (_) , the column name, another underscore (_), and fkey. In our example, the name of the foreign key constraint is student_country_id_fkey, because the column country_id in the table student is a foreign key.

The default name for a CHECK constraint is similar to that of a foreign key, only that it is of the constraint type c. In our example, the column age in the table student has this constraint, so the default constraint name is student_age_check.

The default name for a UNIQUE constraint (type u) is the table name, an underscore (_), the column name, another underscore (_), and key. In our example, the table student has a UNIQUE constraint named student_personal_number_key. For multiple columns in a constraint, the name follows a similar convention but contains the names of all columns in the definition of the constraint.