Thread: Unique index

On Wed, 2023-09-20 at 19:16 +0530, Rajesh Kumar wrote:
> Why unique index is used over unique constraints?

A unique constraint is implemented by a unique index.
If you create the constraint, the index is created automatically.
You cannot drop the index unless you drop the constraint.

Yours,
Laurenz Albe

On 2023-09-20 19:16 +0530, Rajesh Kumar wrote:
> Why unique index is used over unique constraints?

Is the question why someone might want to use a unique index instead of
a unique constraint?  The index can be functional, e.g.

    CREATE TABLE account (username text NOT NULL);
    CREATE UNIQUE INDEX account_lower_username_idx ON account (lower(username));

will make the username unique and case-insensitive (depending on the
collation and locale).

-- 
Erik

Why unique index is used over unique constraints?