Re: Compound keys and foreign constraints

On Thu, 01 Apr 2004 10:53:40 -0600, <wespvp@syntegra.com> wrote:
>db=> explain select

EXPLAIN ANALYSE please ...

>ERROR:  there is no unique constraint matching given keys for referenced
>table "messages"

>It should be able to use the new compound index, as message_key is the top
>index key.

Don't confuse the *concept* of unique constraints with the
*implementation detail* of unique btree indices.  Per SQL standard you
need a unique constraint on the target column to make sure that only one
row matches any referencing value.

The target column being a part of a non-unique index (or even a unique
index) is not sufficient.

Servus
 Manfred