Unique constraint vs. unique index?

Question

Is there a difference between:

CREATE TABLE p(
    product_no integer,
    name text UNIQUE,
    price numeric
);

and:

CREATE TABLE p(
        product_no integer,
        name text,
        price numeric
 );
CREATE UNIQUE INDEX customername
  ON p
  USING btree
  (name COLLATE pg_catalog."default");

Will name be unique in both cases? What does it means when an index is unique?

EDIT: Postgres unique constraint vs index isn't answering my question. It considers a case with FK. My question has nothing to do with FK's. I just want to know if these two operations are equivalent in this example where no FK is involved.

They are functionally the same. A unique constraint creates a unique index. (I'm trying to think if there is a circumstance where the default collation would *not* be used, but I don't there is any.) — Gordon Linoff, Oct 01 '15 at 11:35
Possible duplicate of [Postgres unique constraint vs index](http://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index) — klin, Oct 01 '15 at 11:36
Maybe different NULL unique treatment? (Just a guess, I don't know postgresql.) — jarlh, Oct 01 '15 at 11:43
@avi It answers thoroughly and exhaustively to both of your questions. I do not know what question you mean? — klin, Oct 01 '15 at 11:51
klin, the answer as craig gave is that - there is no diffrance. Please show me how do you get that from the "duplicated" question. — avi, Oct 01 '15 at 12:06
@avi Craig could not have written that there is no difference, because that would not be true. On the contrary, he mentions three differences. I think you have read inattentively, both his answer and referenced post. — klin, Oct 01 '15 at 12:16

score 13 · Accepted Answer · answered Oct 01 '15 at 11:49

13

Yes, there's a small difference. If you define a unique constraint it's visible in catalogs like information_schema. This is not true of a unique index.

Also, you can create things like partial unique indexes, but you cannot do that on a constraint.

Finally, unique constraints are SQL-standard.

A unique constraint implies the creation of a unique index, but not vice versa.

Use a unique constraint unless you have a good reason to create the unique index directly.

answered Oct 01 '15 at 11:49

Craig Ringer

307,061
76
688
778

2

The only reason I've found to use unique indices over unique constraints is the ability to leverage `IF NOT EXISTS`. I gather it's not a great argument, but just sharing my experience for quick development workflows. – Olshansky May 12 '22 at 04:10

score 4 · Answer 2 · answered Oct 01 '15 at 11:49

4

From documentation

Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint

So for your simplified example they are equivalent

answered Oct 01 '15 at 11:49

Lukasz Szozda

162,964
23
234
275

Unique constraint vs. unique index?

2 Answers2

Linked