PostgreSQL: what's the difference between "alter table ... add unique (...)" and "alter table ... add constraint ... unique (...)"?

Question

According to tests both SQL give the same result.

alter table public.company add constraint company_name_key unique ("name")

vs.

alter table public.company add unique ("name");

PostgreSQL (ver. 12.16)

Answer 1

The standard ISO SQL language admit those two syntaxes. In the first one you choose the name of the contraint while the second one will generate a name for the constraint by an internal RDBMS process.

When altering/droping a constraint you need to give the name of the constraint. It is why I will advocate the first one.

Answer 2

There is no difference, because you decided to use the same constraint name that PostgreSQL uses by default. The error message will be

ERROR:  duplicate key value violates unique constraint "company_name_key"

in both cases. You'll use the version where you can decide for a name, when you want a different name, say every constraint name starting with c_.

When working with large databases with many tables and a lot of hierarchie, table names may become very long and column names, too, in order to be descriptive. Let's say you have a table named customer_order_item_customs_tariff with a column called customs_tariff_document_number. PostgreSQL will call your constraint

customer_order_item_customs_t_customs_tariff_document_numbe_key

but maybe you prefer something like

c_corderitemtariff_docno

In my opinion PostgreSQL does a very good job here. I like the automatically generated names. But if you don't, use the verbose version of ALTER TABLE to add a unique constraint matching your naming conventions or likes.