What is the fundamental difference between unique (constraints) and unique index?

Question

PostgreSQL

Hello, I did many approaches to understanding this topic - it did not help.

I read the documentation and did not understand the difference:

www.postgresql.org/docs/9.1/static/ddl-constraints.html
www.postgresql.org/docs/9.1/static/sql-createindex.html

Please explain what is the fundamental difference?

Answer 1

The difference is that restrictions (constraints) are designed to ensure the integrity of the data, and indexes (index) - the speed of access to data. These are two completely unrelated entities. Moreover, if the first is part of the SQL standard, then the second is not (because it has nothing to do with the functionality of the language, the introduction of indexes is a necessary measure). The developer himself decides in which cases to apply these mechanisms and the use of one does not at all require the use of the other.
Now regarding uniqueness (unique). In this case, when adding a unique constraint, Postgresql itself assigns an index to the specified field. This is just a feature of the implementation in this DBMS. The developers decided that this is how it will work and that's it (and not unreasonably). In another similar situation, they decided that the developer himself thinks whether he needs to use these two mechanisms together or not. I'm talking about a foreign key integrity constraint. In Postgresql, indexes on fields with this type of constraint are not created ( Indexes on Foreign Keys in Postgresql ). And, for example, in MySQL are created. This is an implementation feature in MySQL.
Therefore, it is important to simply understand that these are not related things, it’s just that in some implementations they are “linked” to each other and the creation of certain types of restrictions leads to the automatic creation of an index.

Answer 2

In fact, by creating an index, you are creating a constraint and vice versa.
When you create a constraint, you cannot control how the index is created. When creating an index, it is a constraint. With all the consequences.
In practice, the only difference I've seen is the difference in error text ;)

Answer 3

There are no fundamental differences. It's just that in practice for "id"-like columns it is better to use unique constraint, and for columns used in sorting and where-conditions, use index. So it's just much clearer and more predictable to get acquainted with the base to another developer.
But in general, magnitudo is right
"In fact, by creating an index, you create a constraint and vice versa."

Answer 4

Here https://www.sql.ru/forum/665893/vopros-po-unique-c... found a more specific (not philosophical) answer.
"It seems to have found ... only when creating a constraint, it becomes possible to create a foreign key, but otherwise, both unique constraint and unique index do not allow duplicate values ​​to be entered .."