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 (
> person_fk INTEGER REFERENCES persons (person_id),
> event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE,
> sort_order INTEGER NOT NULL DEFAULT 1,
> is_principal BOOLEAN NOT NULL DEFAULT TRUE,
> PRIMARY 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 probably
> only be for a few per cent, I try to create an extra table like this:
>
> pgslekt=> CREATE TABLE participant_notes (
> pgslekt(> person_fk INTEGER NOT NULL REFERENCES participants
> (person_fk),
> pgslekt(> event_fk INTEGER NOT NULL REFERENCES participants (event_fk)
> ON DELETE CASCADE,
> pgslekt(> part_note TEXT,
> pgslekt(> PRIMARY KEY (person_fk, event_fk)
> pgslekt(> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "participant_notes_pkey" for table "participant_notes"
> ERROR: there is no unique constraint matching given keys for referenced table
> "participants"
>
> I fail to see what is the problem. I even tried to add a unique constraint 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.
