What is an Index in PSQL?
An index is unique if for each indexed value there is exactly one matching row in the table.
There are several different ways to create a unique index. First, PostgreSQL automatically creates a unique index to support any primary key or unique constraint on a table.
What is the difference between a primary key and a unique constraint? A common misconception among SQL developers is that a primary key has to be an incrementing numeric value and that each table “has” to have a primary key. Although it often helps to have a numeric incremental primary key (called a surrogate key), a primary key does not have to be numeric, and moreover, it does not have to be a single-attribute constraint.
It is possible to define a primary key as a combination of several attributes; it just has to satisfy two conditions: the combination must be UNIQUE and NOT NULL for all of the participating attributes.
In contrast, unique constraints in PostgreSQL allow for NULL values.
A table can have a single primary key (though a primary key is not required) and multiple unique constraints. Any non-null unique constraint can be chosen to be
a primary key for a table; thus, there is no programmatic way to determine the best candidate for a table’s primary key
Top comments (0)