3

This may be a very silly question, but I have never used unique indexes: I have used unique constraints, though.

I read the related question Unique index vs. unique constraint that uses a unique index but still can't really understand why unique indexes exist.

To me UNIQUE sounds more like a constraint than as an index. Shouldn't it always be specified as a constraint? Or am I missing something here? (most likely). So in simple words the question I have is:

  • What's the use of a unique index?

Maybe the answer lies in history books and is related on how they were chronologically developed.

The Impaler
  • 45,731
  • 9
  • 39
  • 76

2 Answers2

3

One thing a unique index can do that a unique constraint cannot is given in the PostgreSQL documentation on unique constraints.

A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

In their partial unique index docs they give an example.

Suppose that we have a table describing test outcomes. We wish to ensure that there is only one "successful" entry for a given subject and target combination, but there might be any number of "unsuccessful" entries. Here is one way to do it:

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • Wow, very interesting. Though, not a standad feature by any means, it seems that indexes are (have been) more tweakable than constraints. – The Impaler Jun 07 '18 at 18:40
  • @TheImpaler `create index` is not part of the SQL standard at all. Indexes are not part of the standard, I assume they consider them an implementation detail. – Schwern Jun 07 '18 at 19:40
2

In general, unique constraints are implemented using unique indexes (as far as I know, regardless of database). The differences between the two are pretty minor -- often having to do only with the name of the constraint in the event of a constraint violation. Some databases let you choose the type of index for a unique index, so that would be another slight difference.

Why do the two exist? I can speculate. The designers of SQL could pretty much agree that the only reasonable way to implement a unique constraint is via a unique index. Given that CREATE INDEX already existed, why not simply allow a unique option as well? After all, the database would need to support the ability to create a unique index.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So, indexes may be more tweakable: that's a good point. And yeah, the answer may be historical. – The Impaler Jun 07 '18 at 18:35
  • "*The designers of SQL...*" the designers of SQL did not address indexes at all. `create index` is not part of the standard. – Schwern Jun 07 '18 at 19:42