pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique index
4+ messages / 4 participants
[nested] [flat]
* Unique index
@ 2023-09-20 13:46 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Rajesh Kumar @ 2023-09-20 13:46 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
Why unique index is used over unique constraints?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unique index
@ 2023-09-20 14:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2023-09-20 14:14 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unique index
@ 2023-09-20 16:45 Erik Wienhold <ewie@ewie.name>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Erik Wienhold @ 2023-09-20 16:45 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unique index
@ 2023-09-20 17:08 David G. Johnston <david.g.johnston@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-09-20 17:08 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-09-20 17:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-20 13:46 Unique index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-20 14:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-20 16:45 ` Erik Wienhold <ewie@ewie.name>
2023-09-20 17:08 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox