DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=nvJIcQlPGJIv3J0X3wT2iUp7tFll2gn0KuOPmCqDZKCsP+CHs41GC7oNzVrFx7flBJ
	xhBVyJPwWAHw5jbfYVX1SRrYNkP86y3kY8/GpN2gdwF1Rgx86gnZFB/vSCG7AZsYluL0
	CffHO2FW0W1SgwCkMCegfuGMS4SYlcZo+Tnjw=
MIME-Version: 1.0
In-Reply-To: <200911041903.36563.leif@solumslekt.org>
References: <200911041903.36563.leif@solumslekt.org>
Date: Wed, 4 Nov 2009 11:24:29 -0700
Message-ID: <dcc563d10911041024s67a3c313w1ed90c415840c2f7@mail.gmail.com>
Subject: Re: DDL problems: Referential issue?
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Leif Biberg Kristensen <leif@solumslekt.org>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Nov 4, 2009 at 11:03 AM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:
> PostgreSQL 8.3.8 on Gentoo Linux.
>
> I've got a junction table:
>
> CREATE TABLE participants (
> =A0 =A0person_fk INTEGER REFERENCES persons (person_id),
> =A0 =A0event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE,
> =A0 =A0sort_order INTEGER NOT NULL DEFAULT 1,
> =A0 =A0is_principal BOOLEAN NOT NULL DEFAULT TRUE,
> =A0 =A0PRIMARY KEY (person_fk, event_fk)
> );
> CREATE INDEX event_key ON participants (event_fk);
> CREATE INDEX person_key ON participants (person_fk);
>
> Now I want to add some text to a few participants, but as this will proba=
bly
> only be for a few per cent, I try to create an extra table like this:
>
> pgslekt=3D> CREATE TABLE participant_notes (
> pgslekt(> =A0 =A0 person_fk =A0 INTEGER NOT NULL REFERENCES participants
> (person_fk),
> pgslekt(> =A0 =A0 event_fk =A0 =A0INTEGER NOT NULL REFERENCES participant=
s (event_fk)
> ON DELETE CASCADE,
> pgslekt(> =A0 =A0 part_note =A0 TEXT,
> pgslekt(> =A0 =A0 PRIMARY KEY (person_fk, event_fk)
> pgslekt(> );
> NOTICE: =A0CREATE TABLE / PRIMARY KEY will create implicit index
> "participant_notes_pkey" for table "participant_notes"
> ERROR: =A0there is no unique constraint matching given keys for reference=
d table
> "participants"
>
> I fail to see what is the problem. I even tried to add a unique constrain=
t to
> participants:

You're referencing a single column, which does not have a unique key
on it.  Being part of a two column unique PK index doesn't count, as
you could have an entry where one column or the other repeats on its
own while the other column changes.  You might want the syntax:

FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
[, ... ] )

where you FK a pair of columns to a pair of other columns.