Date: Mon, 19 Aug 2002 10:41:21 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Darrin Domoney <ddomoney@emergingfrontiers.ca>
Cc: <pgsql-sql@postgresql.org>, <pgsql-novice@postgresql.org>,
	<pgsql-admin@postgresql.org>
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)
In-Reply-To: <FEEFJMJKKPINIKCCCCBJKEFDCAAA.ddomoney@emergingfrontiers.ca>
Message-ID: <20020819103903.D32160-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Mon, 19 Aug 2002, Darrin Domoney wrote:

> An admitted newbie to postgresql I am trying to commit a new design
> my development server using pgAdminII.
>
> Everything appears to work OK but I am having real grief with my
> SQL generating errors - most of which I have cleared myself but
> one that I am unsure how to handle:
>
> UNIQUE constraint for matching given keys for referenced table "staff"
> not found
>
> Below is the SQL code that I am tring to load to build out my database
> skeleton:
>

> CREATE TABLE staff
> (
>   staff_id serial NOT NULL,
>   person_id int NOT NULL,
>   active_staff boolean NOT NULL,
>   pay_rate decimal(8,2),
>   discounted_rate decimal(8,2),
>   discount_break int,
>   organization_id int NOT NULL,
>   PRIMARY KEY (staff_id)
> );

> ALTER TABLE phone_number ADD CONSTRAINT staff_phone
>   FOREIGN KEY ( person_id )
>    REFERENCES staff ( person_id )
>     NOT DEFERRABLE;

The target of a references constraint must be in a unique
constraint.  Here you're referencing person_id which
is not the key of staff.  Are you sure you don't want
to be linking staff_id instead?