Re: Difference between UNIQUE constraint vs index

Is there any difference as far as when the "uniqueness" of values is
checked in DML between a unique index vs a unique constraint? Or is
the only difference syntax between unique indices and constraints in
PostgreSQL?

John

On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote:
> Is there any difference as far as when the "uniqueness" of values is
> checked in DML between a unique index vs a unique constraint? Or is
> the only difference syntax between unique indices and constraints in
> PostgreSQL?

Syntax only, AFAIK. I prefer using constraints if I actually want to
constrain the data; it makes it clear that it's a restriction.

In some databases if you know that an index just happens to be unique
you might gain some query performance by defining the index as unique,
but I don't think the PostgreSQL planner is that smart. There can also
be some additional overhead involved with a unique index (vs
non-unique), such as when two backends try and add the same key at the
same time (one of them will have to block).
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

John Jawed wrote:
> Is there any difference as far as when the "uniqueness" of values is
> checked in DML between a unique index vs a unique constraint? Or is
> the only difference syntax between unique indices and constraints in
> PostgreSQL?

They are functionally the same and unique constraint will create a
unique index.

Joshua D. Drake

>
> John
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

This is more or less correct, I was looking for performance gains on
the [possible] differences during DML and DDL.

If Jim is correct, is there a particular reason that PostgreSQL does
not behave like other RDBMs without a SET ALL DEFERRED? Or is this a
discussion best left for -HACKERS?

On 2/27/07, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
> On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote:
> > Is there any difference as far as when the "uniqueness" of values is
> > checked in DML between a unique index vs a unique constraint? Or is
> > the only difference syntax between unique indices and constraints in
> > PostgreSQL?
>
> Syntax only, AFAIK. I prefer using constraints if I actually want to
> constrain the data; it makes it clear that it's a restriction.
>
> In some databases if you know that an index just happens to be unique
> you might gain some query performance by defining the index as unique,
> but I don't think the PostgreSQL planner is that smart. There can also
> be some additional overhead involved with a unique index (vs
> non-unique), such as when two backends try and add the same key at the
> same time (one of them will have to block).
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>

Adding -general back in.

On Tue, Feb 27, 2007 at 07:19:15PM -0600, John Jawed wrote:
> This is more or less correct, I was looking for performance gains on
> the [possible] differences during DML and DDL.
>
> If Jim is correct, is there a particular reason that PostgreSQL does
> not behave like other RDBMs without a SET ALL DEFERRED? Or is this a
> discussion best left for -HACKERS?

Well, currently only FK constraints support deferred. And IIRC it's not
generally a performance gain, anyway.

What I was trying to say is that if you're running a query (generally a
SELECT) with certain conditions, the planner can make use of the
knowledge that a column or set of columns is guaranteed to be unique.
PostgreSQL currently can't do that.

> John
>
> On 2/27/07, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
> >On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote:
> >> Is there any difference as far as when the "uniqueness" of values is
> >> checked in DML between a unique index vs a unique constraint? Or is
> >> the only difference syntax between unique indices and constraints in
> >> PostgreSQL?
> >
> >Syntax only, AFAIK. I prefer using constraints if I actually want to
> >constrain the data; it makes it clear that it's a restriction.
> >
> >In some databases if you know that an index just happens to be unique
> >you might gain some query performance by defining the index as unique,
> >but I don't think the PostgreSQL planner is that smart. There can also
> >be some additional overhead involved with a unique index (vs
> >non-unique), such as when two backends try and add the same key at the
> >same time (one of them will have to block).
> >--
> >Jim Nasby jim(at)nasby(dot)net
> >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
>

--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> In some databases if you know that an index just happens to be unique
> you might gain some query performance by defining the index as unique,
> but I don't think the PostgreSQL planner is that smart.

Actually, the planner only pays attention to whether indexes are unique;
the notion of a unique constraint is outside its bounds. In PG a unique
constraint is implemented by creating a unique index, and so there is
really not any interesting difference.

I would imagine that other DBMSes also enforce uniqueness by means of
indexes, because it'd be awful darn expensive to enforce the constraint
without one; but I'm only guessing here, not having looked. Can anyone
point to a real system that enforces unique constraints without an
underlying index?

regards, tom lane

SQLite, MySQL, and MS Access each use indexes for unique constraints.

Doesn't the SQL spec specify that CREATE INDEX can be used to create
UNIQUE indexes? Are there any real systems that don't support indexes
but that support unique? It seems silly, since the code for a primary
key is a superset of what's needed for unique, so I would expect only
legacy systems to support non-indexed uniques. Any newer DBMS would
implement primary keys and then steal the code for uniques.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, February 28, 2007 1:17 AM
To: Jim C. Nasby
Cc: John Jawed; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Difference between UNIQUE constraint vs index

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> In some databases if you know that an index just happens to be unique
> you might gain some query performance by defining the index as unique,
> but I don't think the PostgreSQL planner is that smart.

Actually, the planner only pays attention to whether indexes are unique;
the notion of a unique constraint is outside its bounds. In PG a unique
constraint is implemented by creating a unique index, and so there is
really not any interesting difference.

I would imagine that other DBMSes also enforce uniqueness by means of
indexes, because it'd be awful darn expensive to enforce the constraint
without one; but I'm only guessing here, not having looked. Can anyone
point to a real system that enforces unique constraints without an
underlying index?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------------------------------------------------------------
** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged,
confidential or proprietary information. If you are not the intended
recipient(s), or the employee or agent responsible for delivery of
this message to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this e-mail
message is strictly prohibited. If you have received this message in
error, please immediately notify the sender and delete this e-mail
message from your computer.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/28/07 00:16, Tom Lane wrote:
> "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
>> In some databases if you know that an index just happens to be unique
>> you might gain some query performance by defining the index as unique,
>> but I don't think the PostgreSQL planner is that smart.
>
> Actually, the planner only pays attention to whether indexes are unique;
> the notion of a unique constraint is outside its bounds. In PG a unique
> constraint is implemented by creating a unique index, and so there is
> really not any interesting difference.
>
> I would imagine that other DBMSes also enforce uniqueness by means of
> indexes, because it'd be awful darn expensive to enforce the constraint
> without one; but I'm only guessing here, not having looked. Can anyone
> point to a real system that enforces unique constraints without an
> underlying index?

In Rdb/VMS (which does not use MVCC), PK (and it's alias UNIQUE)
constraints are independent of whether you have a unique index on
the table.

Now, 99.44% of the time you will *not* have a PK constraint, but
simply a unique index.

The other 0.56% of the time, you define a situation where the index
records and table records are clustered onto the same page using a
*non*-unique hashed index. This, obviously, means that multiple
table records will be stored on the same page. You then create a PK
constraint that is a superset of the non-unique hashed index.
Rdb/VMS will use the hashed index to read that whole page into the
buffer pool and the CPU will do the grunge work of determining
"primaryness". I've only ever done this in OLTP situations.

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF5ZrQS9HxQb37XmcRAtzzAKDBg2h8kp70xq1XTyPr/DjIn6HUYwCfd/A8
V4Af3Szc9xzK1TXMsEIV7U8=
=vVIS
-----END PGP SIGNATURE-----

"Brandon Aiken" <BAiken(at)winemantech(dot)com> writes:
> SQLite, MySQL, and MS Access each use indexes for unique constraints.
> Doesn't the SQL spec specify that CREATE INDEX can be used to create
> UNIQUE indexes?

No, there is no such command in the SQL spec. In fact the concept of an
index does not appear anywhere in the spec ... it's an implementation
detail.

regards, tom lane

>>> I would imagine that other DBMSes also enforce uniqueness by means of
>>> indexes, because it'd be awful darn expensive to enforce the constraint
>>> without one; but I'm only guessing here, not having looked. Can anyone
>>> point to a real system that enforces unique constraints without an
>>> underlying index?
>
> In Rdb/VMS (which does not use MVCC), PK (and it's alias UNIQUE)
> constraints are independent of whether you have a unique index on
> the table.

PK is NOT an alias for UNIQUE. Yes it does have the same functional
operation but it is technically incorrect to consider them the same.

>
> Now, 99.44% of the time you will *not* have a PK constraint, but
> simply a unique index.

Then you have designed your database incorrectly.

Sincerely,

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/28/07 10:31, Joshua D. Drake wrote:
>>>> I would imagine that other DBMSes also enforce uniqueness by means of
>>>> indexes, because it'd be awful darn expensive to enforce the constraint
>>>> without one; but I'm only guessing here, not having looked. Can anyone
>>>> point to a real system that enforces unique constraints without an
>>>> underlying index?
>>
>> In Rdb/VMS (which does not use MVCC), PK (and it's alias UNIQUE)
>> constraints are independent of whether you have a unique index on
>> the table.
>
> PK is NOT an alias for UNIQUE. Yes it does have the same functional
> operation but it is technically incorrect to consider them the same.

In Rdb/VMS, which I was describing, the PK and UNIQUE constraints
(which are *not* the same as unique index) *are*

>> Now, 99.44% of the time you will *not* have a PK constraint, but
>> simply a unique index.
>
> Then you have designed your database incorrectly.

Or... *you* don't understand Rdb, and the circumstances in which it
is used.

In Rdb, a defining a PK has no automagic side effects (Which I
heartily approve of). The DBA is responsible for knowing the data
and determining the best (of multiple) way to ensuring that *that*
set of data is.

So, if you would already have put a unique index on that table,
there's no reason to also put a PK constraint on in (unless there
will also be an FK reference).

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF5ba+S9HxQb37XmcRApEOAJ9x6pco5kgqc2alEVGlEHRyOyC2WQCfQCp4
JaXCNqn0UgJGl91Kb4Suq54=
=tAbk
-----END PGP SIGNATURE-----

Informix:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls285.htm

AFAICS, Oracle as well.

John

On 2/28/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> > In some databases if you know that an index just happens to be unique
> > you might gain some query performance by defining the index as unique,
> > but I don't think the PostgreSQL planner is that smart.
>
> Actually, the planner only pays attention to whether indexes are unique;
> the notion of a unique constraint is outside its bounds. In PG a unique
> constraint is implemented by creating a unique index, and so there is
> really not any interesting difference.
>
> I would imagine that other DBMSes also enforce uniqueness by means of
> indexes, because it'd be awful darn expensive to enforce the constraint
> without one; but I'm only guessing here, not having looked. Can anyone
> point to a real system that enforces unique constraints without an
> underlying index?
>
> regards, tom lane
>

Problem number 6,534 with implementing an abstract concept such as an
RDB on a digital computer with an electro-magno-mechanical storage
system.

:p

--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, February 28, 2007 11:03 AM
To: Brandon Aiken
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Difference between UNIQUE constraint vs index

"Brandon Aiken" <BAiken(at)winemantech(dot)com> writes:
> SQLite, MySQL, and MS Access each use indexes for unique constraints.
> Doesn't the SQL spec specify that CREATE INDEX can be used to create
> UNIQUE indexes?

No, there is no such command in the SQL spec. In fact the concept of an
index does not appear anywhere in the spec ... it's an implementation
detail.

regards, tom lane

--------------------------------------------------------------------
** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged,
confidential or proprietary information. If you are not the intended
recipient(s), or the employee or agent responsible for delivery of
this message to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this e-mail
message is strictly prohibited. If you have received this message in
error, please immediately notify the sender and delete this e-mail
message from your computer.