What Is the Default Constraint Name in PostgreSQL? Database: PostgreSQL Operators: CONSTRAINT PRIMARY KEY FOREIGN KEY UNIQUE DEFAULT CHECK 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). 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 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: connamecontype country_pkeyp country_name_keyu student_age_checkc student_pkeyp student_personal_number_keyu student_country_id_fkeyf 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. Recommended courses: The Basics of Creating Tables Data Types in SQL SQL Constraints Recommended articles: What is a Primary Key in SQL? How to Create a Table in SQL Referential Constraints and Foreign Keys in MySQL Understanding Numerical Data Types in SQL See also: How to Create a Primary Key in SQL How to Remove a Primary Key in SQL How to Find the Name of a Constraint in MySQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query