Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Urgent - SQL Unique constraint error (long)

From: "Darrin Domoney" <ddomoney(at)emergingfrontiers(dot)ca>
To: <pgsql-sql(at)postgresql(dot)org>, <pgsql-novice(at)postgresql(dot)org>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)
Date: 2002-08-19 18:25:24
Message-ID: FEEFJMJKKPINIKCCCCBJEEFFCAAA.ddomoney@emergingfrontiers.ca (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-novicepgsql-sql
Stephan,
	Thanks for the response but the answer is no. Owing to the 
ongoing issue with inherited tables in 7.x I have opted to create three
tables:
Contains generic traits regardless of "class or role".
Person -> PK person_id

Staff as certain "class" of person.
Staff -> PK staff_id
	   FK person_id

Contact as another "class" of person.
Contact -> PK contact_id
           FK person_id

Phone numbers relate to any "class" but are related back to 
the originator by using "person_id".

Darrin
	     


-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
Sent: August 19, 2002 12:41 PM
To: Darrin Domoney
Cc: pgsql-sql(at)postgresql(dot)org; pgsql-novice(at)postgresql(dot)org;
pgsql-admin(at)postgresql(dot)org
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)


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?





In response to

Responses

pgsql-novice by date

Next:From: Darrin DomoneyDate: 2002-08-19 18:28:38
Subject: Re: [NOVICE] Urgent - SQL Unique constraint error (long)
Previous:From: paul butlerDate: 2002-08-19 18:10:50
Subject: Re: Urgent - SQL Unique constraint error (long)

pgsql-admin by date

Next:From: Darrin DomoneyDate: 2002-08-19 18:28:38
Subject: Re: [NOVICE] Urgent - SQL Unique constraint error (long)
Previous:From: paul butlerDate: 2002-08-19 18:10:50
Subject: Re: Urgent - SQL Unique constraint error (long)

pgsql-sql by date

Next:From: Darrin DomoneyDate: 2002-08-19 18:28:38
Subject: Re: [NOVICE] Urgent - SQL Unique constraint error (long)
Previous:From: paul butlerDate: 2002-08-19 18:10:50
Subject: Re: Urgent - SQL Unique constraint error (long)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group