Question 298633 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search