CODEWITHSUNDEEP
postgresqlnaming-conventionsconstraints
Ian Mackinnon
Ian Mackinnon

Reputation: 14238

PostgreSQL: default constraint names

When creating a table in PostgreSQL, default constraint names will assigned if not provided:

CREATE TABLE example (
    a integer,
    b integer,
    UNIQUE (a, b)
);

But using ALTER TABLE to add a constraint it seems a name is mandatory:

ALTER TABLE example ADD CONSTRAINT my_explicit_constraint_name UNIQUE (a, b);

This has caused some naming inconsistencies on projects I've worked on, and prompts the following questions:

  1. Is there a simple way to add a constraint to an extant table with the name it would have received if added during table creation?

  2. If not, should default names be avoided altogether to prevent inconsistencies?

Upvotes: 141

Views: 72765

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127137

The standard names for indexes in PostgreSQL are:

{tablename}_{columnname(s)}_{suffix}

where the suffix is one of the following:

  • pkey for a Primary Key constraint
  • key for a Unique constraint
  • excl for an Exclusion constraint
  • idx for any other kind of index
  • fkey for a Foreign key
  • check for a Check constraint

Standard suffix for sequences is

  • seq for all sequences

Proof of your UNIQUE-constraint:

NOTICE: CREATE TABLE / UNIQUE will create implicit index "example_a_b_key" for table "example"

Upvotes: 409

user330315
user330315

Reputation:

The manual is pretty clear about this ("tableconstraint: This form adds a new constraint to a table using the same syntax as CREATE TABLE.")

So you can simply run:

ALTER TABLE example ADD UNIQUE (a, b);

Upvotes: 51

Related Questions