Urgent - SQL Unique constraint error (long)
Urgent - SQL Unique constraint error (long)
От:
"Darrin Domoney" <ddomoney@emergingfrontiers.ca>
Дата:
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 person
(
person_id serial NOT NULL,
fname text NOT NULL,
lname text NOT NULL,
aka_name text,
PRIMARY KEY (person_id)
);
CREATE TABLE phone_number
(
phone_number_id serial NOT NULL,
person_id int NOT NULL,
phone_type_id int NOT NULL,
area_code varchar(3),
phone_number varchar(7) NOT NULL,
phone_extension varchar(4),
PRIMARY KEY (phone_number_id)
);
CREATE TABLE phone_type
(
phone_type_id serial NOT NULL,
phone_type_desc text NOT NULL,
PRIMARY KEY (phone_type_id)
);
CREATE TABLE address
(
address_id serial NOT NULL,
address_type_id int NOT NULL,
person_id int NOT NULL,
address1 text,
address2 text,
address3 text,
post_code varchar(10),
city_id int,
province_id int,
country_id int,
PRIMARY KEY (address_id)
);
CREATE TABLE city
(
city_id serial NOT NULL,
city_name text NOT NULL,
PRIMARY KEY (city_id)
);
CREATE TABLE address_type
(
address_type_id serial NOT NULL,
address_type_desc text NOT NULL,
PRIMARY KEY (address_type_id)
);
CREATE TABLE province
(
province_id serial NOT NULL,
province varchar(2) NOT NULL,
PRIMARY KEY (province_id)
);
CREATE TABLE country
(
country_id serial NOT NULL,
country text NOT NULL,
PRIMARY KEY (country_id)
);
CREATE TABLE email
(
email_id serial NOT NULL,
email_type_id int NOT NULL,
person_id int NOT NULL,
email text NOT NULL,
PRIMARY KEY (email_id)
);
CREATE TABLE email_type
(
email_type_id serial NOT NULL,
email_type text NOT NULL,
PRIMARY KEY (email_type_id)
);
CREATE TABLE skills
(
staff_id int NOT NULL,
skill_type_id int NOT NULL,
PRIMARY KEY (staff_id,skill_type_id)
);
CREATE TABLE skills_type
(
skills_type_id serial NOT NULL,
skill_desc text NOT NULL,
PRIMARY KEY (skills_type_id)
);
CREATE TABLE leave
(
leave_id serial NOT NULL,
staff_id int NOT NULL,
leave_type_id int NOT NULL,
date_from date NOT NULL,
date_to date NOT NULL,
time_from time NOT NULL,
time_to time NOT NULL,
PRIMARY KEY (leave_id)
);
CREATE TABLE leave_type
(
leave_type_id serial NOT NULL,
leave_type text NOT NULL,
PRIMARY KEY (leave_type_id)
);
CREATE TABLE event
(
event_id serial NOT NULL,
staff_id int NOT NULL,
client_id int NOT NULL,
requestor_id int NOT NULL,
assign_type_id int NOT NULL,
assign_subtype_id int,
requested_date date NOT NULL,
requested_start time NOT NULL,
requested_end time NOT NULL,
location text NOT NULL,
notes text,
event_status_id int NOT NULL,
probono boolean,
sys_date timestamp NOT NULL,
PRIMARY KEY (event_id)
);
CREATE TABLE organization
(
organization_id serial NOT NULL,
org_type_id int NOT NULL,
organization_name text NOT NULL,
department text,
short_name text NOT NULL,
PRIMARY KEY (organization_id)
);
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)
);
CREATE TABLE contact
(
contact_id serial NOT NULL,
person_id int NOT NULL,
organization_id int,
client boolean NOT NULL,
PRIMARY KEY (contact_id)
);
CREATE TABLE assignment_type
(
assign_type_id serial NOT NULL,
assign_type_desc text NOT NULL,
PRIMARY KEY (assign_type_id)
);
CREATE TABLE assignment_subtype
(
assign_subtype_id serial NOT NULL,
assign_subtype_desc text NOT NULL,
PRIMARY KEY (assign_subtype_id)
);
CREATE TABLE resource
(
resource_id serial NOT NULL,
event_id int NOT NULL,
requested_resource_type_id int NOT NULL,
assigned_resource_id int,
scheduled_date date,
scheduled_start time,
scheduled_end time,
actual_start time,
actual_end time,
PRIMARY KEY (resource_id)
);
CREATE TABLE event_status
(
event_status_id serial NOT NULL,
event_status_desc text NOT NULL,
PRIMARY KEY (event_status_id)
);
CREATE TABLE organization_type
(
org_type_id serial NOT NULL,
org_type_desc text NOT NULL,
PRIMARY KEY (org_type_id)
);
CREATE TABLE event_replication
(
trigger_id int NOT NULL,
result_event_id int NOT NULL,
replication_id serial NOT NULL,
PRIMARY KEY (replication_id)
);
-- +---------------------------------------------------------
-- | FOREIGN KEYS
-- +---------------------------------------------------------
ALTER TABLE phone_number ADD CONSTRAINT staff_phone
FOREIGN KEY ( person_id )
REFERENCES staff ( person_id )
NOT DEFERRABLE;
ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number
FOREIGN KEY ( person_id )
REFERENCES contact ( person_id )
NOT DEFERRABLE;
ALTER TABLE phone_number ADD CONSTRAINT phone_number_type
FOREIGN KEY ( phone_type_id )
REFERENCES phone_type ( phone_type_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT contact_address
FOREIGN KEY ( person_id )
REFERENCES contact ( person_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT staff_address
FOREIGN KEY ( person_id )
REFERENCES staff ( person_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT address_country
FOREIGN KEY ( country_id )
REFERENCES country ( country_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT address_province
FOREIGN KEY ( province_id )
REFERENCES province ( province_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT address_city
FOREIGN KEY ( city_id )
REFERENCES city ( city_id )
NOT DEFERRABLE;
ALTER TABLE address ADD CONSTRAINT address_type
FOREIGN KEY ( address_type_id )
REFERENCES address_type ( address_type_id )
NOT DEFERRABLE;
ALTER TABLE email ADD CONSTRAINT contact_email
FOREIGN KEY ( person_id )
REFERENCES contact ( person_id )
NOT DEFERRABLE;
ALTER TABLE email ADD CONSTRAINT staff_email
FOREIGN KEY ( person_id )
REFERENCES staff ( person_id )
NOT DEFERRABLE;
ALTER TABLE email ADD CONSTRAINT email_type
FOREIGN KEY ( email_type_id )
REFERENCES email_type ( email_type_id )
NOT DEFERRABLE;
ALTER TABLE leave ADD CONSTRAINT staff_leave
FOREIGN KEY ( staff_id )
REFERENCES staff ( staff_id )
NOT DEFERRABLE;
ALTER TABLE leave ADD CONSTRAINT leave_type_lookup
FOREIGN KEY ( leave_type_id )
REFERENCES leave_type ( leave_type_id )
NOT DEFERRABLE;
ALTER TABLE event ADD CONSTRAINT event_assignment_subtype
FOREIGN KEY ( assign_subtype_id )
REFERENCES assignment_subtype ( assign_subtype_id )
NOT DEFERRABLE;
ALTER TABLE event ADD CONSTRAINT event_assignment_type
FOREIGN KEY ( assign_type_id )
REFERENCES assignment_type ( assign_type_id )
NOT DEFERRABLE;
ALTER TABLE event ADD CONSTRAINT staff_event
FOREIGN KEY ( staff_id )
REFERENCES staff ( staff_id )
NOT DEFERRABLE;
ALTER TABLE event ADD CONSTRAINT requestor_event
FOREIGN KEY ( requestor_id )
REFERENCES contact ( contact_id )
NOT DEFERRABLE;
ALTER TABLE event ADD CONSTRAINT client_event
FOREIGN KEY ( client_id )
REFERENCES contact ( contact_id )
NOT DEFERRABLE;
ALTER TABLE organization ADD CONSTRAINT organization_type
FOREIGN KEY ( org_type_id )
REFERENCES organization_type ( org_type_id )
NOT DEFERRABLE;
ALTER TABLE staff ADD CONSTRAINT staff_person
FOREIGN KEY ( person_id )
REFERENCES person ( person_id )
NOT DEFERRABLE;
ALTER TABLE staff ADD CONSTRAINT staff_organization
FOREIGN KEY ( organization_id )
REFERENCES organization ( organization_id )
NOT DEFERRABLE;
ALTER TABLE contact ADD CONSTRAINT contact_organization
FOREIGN KEY ( organization_id )
REFERENCES organization ( organization_id )
NOT DEFERRABLE;
ALTER TABLE contact ADD CONSTRAINT contact_person
FOREIGN KEY ( person_id )
REFERENCES person ( person_id )
NOT DEFERRABLE;
ALTER TABLE resource ADD CONSTRAINT resource_staff
FOREIGN KEY ( assigned_resource_id )
REFERENCES staff ( staff_id )
NOT DEFERRABLE;
ALTER TABLE resource ADD CONSTRAINT event_resource
FOREIGN KEY ( event_id )
REFERENCES event ( event_id )
NOT DEFERRABLE;
ALTER TABLE resource ADD CONSTRAINT resource_skill_type
FOREIGN KEY ( requested_resource_type_id )
REFERENCES skills_type ( skills_type_id )
NOT DEFERRABLE;
ALTER TABLE event_status ADD CONSTRAINT event_status
FOREIGN KEY ( event_status_id )
REFERENCES event ( event_status_id )
NOT DEFERRABLE;
ALTER TABLE event_replication ADD CONSTRAINT event_replication
FOREIGN KEY ( trigger_id )
REFERENCES event ( event_id )
NOT DEFERRABLE;
ALTER TABLE event_replication ADD CONSTRAINT replication_result
FOREIGN KEY ( result_event_id )
REFERENCES event ( event_id )
NOT DEFERRABLE;
Any other suggestions or recommendations here are more than welcome.
Thanks,
Darrin
Re: [SQL] Urgent - SQL Unique constraint error (long)
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
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?
Re: [SQL] Urgent - SQL Unique constraint error (long)
От:
"Darrin Domoney" <ddomoney@emergingfrontiers.ca>
Дата:
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@megazone23.bigpanda.com]
Sent: August 19, 2002 12:41 PM
To: Darrin Domoney
Cc: pgsql-sql@postgresql.org; pgsql-novice@postgresql.org;
pgsql-admin@postgresql.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?
Re: [SQL] Urgent - SQL Unique constraint error (long)
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
On Mon, 19 Aug 2002, Darrin Domoney wrote:
> 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".
I now see what you're doing, but it won't work.
ALTER TABLE phone_number ADD CONSTRAINT staff_phone
FOREIGN KEY ( person_id )
REFERENCES staff ( person_id )
NOT DEFERRABLE;
ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number
FOREIGN KEY ( person_id )
REFERENCES contact ( person_id )
NOT DEFERRABLE;
means that the person_id in phone number must be in
*both* contact and staff.
Are there classes of person that you don't want phone_number
to be able to reference? If not, you should be referencing
person(person_id). If so, I'm not sure I have an answer for
you apart from hacking triggers since even if inheritance
worked, it wouldn't really help you there.