From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Lew <lew(at)nospam(dot)lewscanon(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: are foreign keys realized as indexes? |
Date: | 2007-05-14 22:09:29 |
Message-ID: | 7B8AD47D-9F19-4A43-9F3A-0BAD84FC6D4B@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-05-14 22:12:55 | Re: Postgres Printed Manuals |
Previous Message | Rich Shepard | 2007-05-14 20:58:41 | Re: Performance issues of one vs. two split tables. |