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, Nov 05 '10 at 16:39

score 371 · Answer 1 · edited Aug 08 '14 at 14:46

371

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"

edited Aug 08 '14 at 14:46

jmagnusson

5,799
4
43
38

answered Nov 05 '10 at 17:10

Frank Heikens

117,544
24
142
135

12

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. – Ian Mackinnon Nov 05 '10 at 17:39
1

@IanMackinnon, This should be the best answer! – Marcio Mazzucato Apr 04 '14 at 20:25
This info is gold! You can confirm these by running e.g `CREATE TABLE mytable (mycolumn numeric UNIQUE);` – jmagnusson Aug 08 '14 at 14:45
See http://stackoverflow.com/a/8674640/14731 if you want to override the default constraint name. – Gili Nov 25 '14 at 05:11
I don't think this is always useful, I liked this article talking about naming conventions here: https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/ – simo Feb 04 '16 at 07:38
9

Exactly the answer I was looking for when googling "postgresql index naming conventions" – Fancy John Oct 14 '16 at 07:24
What will the generated name be for the second index on a column? Will it be `idx2`? – davidtgq Oct 12 '17 at 16:15
@IanMackinnon This was changed/fixed in version 12. It's mentioned at the end of section E.3.3.3 in [the release notes](https://www.postgresql.org/docs/12/release-12.html) – Michael Hewson May 07 '20 at 23:25
@FrankHeikens This is very useful, I would like to know is there any official document or reference to cite?? – someone Jun 02 '20 at 05:15
@MichaelHewson I can't find section `E3.3.3` :( The link above seems like ends of `E.3.2. Changes`. – someone Jun 02 '20 at 05:19
1

@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)" – Michael Hewson Jun 03 '20 at 15:48
@MichaelHewson thanks! Looks like the document update very frequently. – someone Jun 03 '20 at 23:44
Exactly what I was looking for! It would be cool to have some examples indexes under each one just so we can see it in action. For foreign keys, is the table name the name of the table the foreign key is in or the table of the key it is referencing For example, if there is a `user_id` foreign key in a `user_media` table. Is the foreign key then `user_media_user_id_fkey`? – Ryker Nov 12 '21 at 22:22
2

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 May 10 '22 at 23:12
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.. – Mike Finch May 11 '22 at 18:06
@FrankHeikens I'm looking for documentation on how long constraint names get truncated, would be nice to have in this answer if it's explicitly documented anywhere. – Andy Oct 05 '22 at 04:23
For unique index, maybe `uidx` is more suitable. – xuxu Jul 11 '23 at 12:59

score 45 · Accepted Answer · answered Nov 05 '10 at 17:37

45

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 05 '10 at 17:37

5

Ah! I was mistakenly including `CONSTRAINT` like `ALTER TABLE example ADD CONSTRAINT UNIQUE (a, b);` and getting errors. Thank you! – Ian Mackinnon Nov 05 '10 at 17:51

PostgreSQL: default constraint names

2 Answers2

Linked