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