Re: are foreign keys realized as indexes?

On May 13, 2007, at 17:21 , Lew wrote:

> Peter Childs wrote:
>> Apart from anything a unique constraint is NOT the same as a
>> unique index, as you need a not null constraint on the column as
>> well.
>
> Not true, whichever way 'round you meant it.

Technically, the UNIQUE constraint is a logical concept which is
physically implemented in PostgreSQL via a unique BTREE index. Since
there is only one way to implement a UNIQUE constraint in PostgreSQL,
the two concepts are very closely tied. However, say one day
PostgreSQL as a unique GiST index implementation. Then there are two
potentially two physical implementations for the UNIQUE constraint.

>
> For pg unique constraint
> <http://www.postgresql.org/docs/8.1/interactive/ddl-
> constraints.html#AEN2016>
>> In general, a unique constraint is violated when there are two or
>> more rows in the table where the values of all of the columns
>> included in the constraint are equal. However, null values are not
>> considered equal in this comparison. That means even in the
>> presence of a unique constraint it is possible to store duplicate
>> rows that contain a null value in at least one of the constrained
>> columns. This behavior conforms to the SQL standard,

Note here, there is no mention of indexes (a implementation issue):
just the logical constraints.

>
> unique index
> <http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
>> When an index is declared unique, multiple table rows with equal
>> indexed values will not be allowed. Null values are not considered
>> equal.

Here, they're making the distinction between unique and non-unique
(BTREE) indexes: implementation.

These are subtle points, but worth distinguishing.


Michael Glaesemann
grzm seespotcode net