Constraint Naming

Objectives

By the end of this lesson, you should be able to:

• Predict the name PostgreSQL generates for an unnamed constraint
• Name a constraint explicitly with CONSTRAINT

💡 Why this matters: Every DROP CONSTRAINT from the last two lessons needs to know a constraint’s exact name. PostgreSQL generates one automatically if you don’t, in a predictable pattern, worth knowing before you go looking for it.

⚠️ A note on verification: every constraint name in this lesson was confirmed against a real, live PostgreSQL 18 database, by querying its system catalog directly.

PostgreSQL’s Default Naming Pattern

Declared without an explicit name, every constraint type gets a predictable, auto-generated one, built from the table name, column name, and constraint type:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    salary NUMERIC(10,2) NOT NULL CHECK (salary > 0),
    department_id INTEGER REFERENCES departments(id)
);

 conname                       | type
--------------------------------+-------------
 employees_pkey                 | PRIMARY KEY
 employees_email_key             | UNIQUE
 employees_email_not_null        | NOT NULL
 employees_salary_check          | CHECK
 employees_salary_not_null       | NOT NULL
 employees_department_id_fkey    | FOREIGN KEY

The pattern: {table}_pkey for a primary key, {table}_{column}_key for UNIQUE, {table}_{column}_check for CHECK, {table}_{column}_fkey for a foreign key, and {table}_{column}_not_null for NOT NULL. Knowing this pattern means being able to guess a constraint’s name well enough to DROP it, without necessarily querying for it first.

Naming a Constraint Explicitly

Use CONSTRAINT name right before the constraint itself, this works the same way inside CREATE TABLE and in ALTER TABLE ... ADD CONSTRAINT (last two lessons):

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget NUMERIC(12,2) NOT NULL,
    CONSTRAINT projects_budget_positive CHECK (budget > 0),
    CONSTRAINT projects_name_unique UNIQUE (name)
);

 conname
--------------------------
 projects_budget_positive
 projects_name_unique
 projects_pkey

projects_budget_positive is far more readable at a glance than the auto-generated projects_budget_check would have been, especially in a larger table with several CHECK constraints where the default names alone wouldn’t distinguish what each one actually enforces.

Try It

1. Create a table with an inline UNIQUE and CHECK constraint, neither one explicitly named, then query the table’s constraints and confirm PostgreSQL’s default names match the pattern from this lesson.
2. Recreate the same table, this time naming both constraints explicitly with CONSTRAINT.
3. Explain, in your own words, one situation where an explicit constraint name would be clearly more useful than an auto-generated one.

Recap

• Unnamed constraints get an auto-generated name: {table}_pkey, {table}_{column}_key, {table}_{column}_check, {table}_{column}_fkey, or {table}_{column}_not_null.
• CONSTRAINT name before a constraint definition names it explicitly, inside CREATE TABLE or ALTER TABLE ... ADD CONSTRAINT.
• A clear, explicit name makes a constraint easier to identify and DROP later, especially in a table with several similar constraints.

Next lesson: this module’s exercises, practicing every kind of ALTER TABLE change from this module together.