Re: Difference between UNIQUE constraint vs index

-----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-----