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

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: Urgent - SQL Unique constraint error (long)
Date: 2002-08-19 17:34:53
Message-ID: FEEFJMJKKPINIKCCCCBJKEFDCAAA.ddomoney@emergingfrontiers.ca (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-novicepgsql-sql
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


Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2002-08-19 17:41:21
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)
Previous:From: Warwick HunterDate: 2002-08-18 23:04:00
Subject: Re: BLOBs

pgsql-admin by date

Next:From: Stephan SzaboDate: 2002-08-19 17:41:21
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)
Previous:From: John MaddenDate: 2002-08-19 16:16:31
Subject: pg_dumpall and output formats?

pgsql-sql by date

Next:From: Stephan SzaboDate: 2002-08-19 17:41:21
Subject: Re: [SQL] Urgent - SQL Unique constraint error (long)
Previous:From: Mark StosbergDate: 2002-08-19 17:13:24
Subject: Re: need assistance with multi-row matching expression

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