Thread: Unique index

Unique index

From
Rajesh Kumar
Date:
Hi

Why unique index is used over unique constraints?

Re: Unique index

From
Laurenz Albe
Date:
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



Re: Unique index

From
Erik Wienhold
Date:
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



Re: Unique index

From
"David G. Johnston"
Date:
On Wednesday, September 20, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Why unique index is used over unique constraints?

It’s rare that you would.  Occasionally people want to enforce uniqueness over a subset of the table - like if you want to ensure a maximum of one active record (for a given id) at any given time but allow for multiple historical records (for the same id) on the same table.

David J.