4

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.

Community
  • 1
  • 1
avi
  • 1,626
  • 3
  • 27
  • 45
  • 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
  • 2
    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
  • @klin that doesn't answer my question. – avi Oct 01 '15 at 11:43
  • 1
    @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

2 Answers2

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.

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275