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

Paul,
See my earlier comments relating to the reasons behind the structure
for the database. I know this is an issue but if inheritance is "not
working"
it seems like my only option.

Darrin

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of paul butler
Sent: August 19, 2002 1:11 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] 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: [NOVICE] Urgent - SQL Unique constraint error (long)
Date sent: Mon, 19 Aug 2002 12:34:53 -0500

This is just a guess but the message indicates that you're not
referencing another key, or unique value, so it can't really tell which
tuple it should be referencing.
Without wanting to offend, It looks like a design problem.
To the immediate question
I would suggest merging the person and staff tables, make the
person_id (you've got an awful lot of these artificial keys there, but I
suppose that is a matter of taste) the primary key and dump the
staff _id, and reference that.

either that or reference person (person_id)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly