PostgreSQL: default constraint names

Question

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:

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?
If not, should default names be avoided altogether to prevent inconsistencies?

I make it a rule to avoid default names for just this reason - you end up with the situation where in every deployment the constraints have different names. — Paul Tomblin
– Paul Tomblin, Commented Nov 5, 2010 at 16:39

Masklinn · Accepted Answer · 2025-09-30 12:59:00Z

420

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
not_null for a Not Null constraint (since Postgres 18)

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"

edited Sep 30 at 12:59

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

answered Nov 5, 2010 at 17:10

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Ian Mackinnon Over a year ago

Very useful, thanks! Worth adding that foreign keys use the suffix fkey and that multi-column foreign key constraints only seem to include the first column name.

Fancy John Over a year ago

Exactly the answer I was looking for when googling "postgresql index naming conventions"

Michael Hewson Over a year ago

@someone ah sorry, I guess I meant the end of E.4.3.3 Utility Commands. It's the last bullet item before E.4.3.4 Data Types: "Use all key columns' names when selecting default constraint names for foreign keys (Peter Eisentraut)"

Mike Finch Over a year ago

Using PostgreSQL version 11, I observed that the default name of a primary key constraint is always {tablename}_pkey, and does not include the name of the column or columns used in the primary key.

Mike Finch Over a year ago

Using PostgreSQL version 11, I observed that the default name of a foreign key constraint is always {tablename}_{first columnname}_fkey,. i.e., regardless of how many columns are used in the foreign key, only the name of the first column is used..

|

user330315 · Accepted Answer · 2010-11-05 17:37:35Z

54

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

answered Nov 5, 2010 at 17:37

user330315

1 Comment

Ian Mackinnon Over a year ago

Ah! I was mistakenly including CONSTRAINT like ALTER TABLE example ADD CONSTRAINT UNIQUE (a, b); and getting errors. Thank you!

Collectives™ on Stack Overflow

PostgreSQL: default constraint names

2 Answers 2

18 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

18 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related