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

2002-08-19 Thread Darrin Domoney 


Stephen,
I preemptivelty sensed your reply ;-) I took another look at the ERD
and quickly revised phone, address, and email so they all reference the
person
without having to become intertwined with their extended "class". Ultimately
this should prove more flexible as it will allow persons to shift from class
to class if their roles change.

Darrin

Inheritance would have made things so much easier .

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Stephan Szabo
Sent: August 19, 2002 1:56 PM
To: Darrin Domoney
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: [ADMIN] [SQL] Urgent - SQL Unique constraint error (long)



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.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

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

2002-08-19 Thread Darrin Domoney 

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

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

2002-08-19 Thread Darrin Domoney 

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:[EMAIL PROTECTED]]
Sent: August 19, 2002 12:41 PM
To: Darrin Domoney
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
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?





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

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

2002-08-19 Thread Darrin Domoney 

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of paul butler
Sent: August 19, 2002 1:11 PM
To: [EMAIL PROTECTED]
Subject: Re: [NOVICE] Urgent - SQL Unique constraint error (long)


From:       "Darrin Domoney" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
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,

[SQL] Event recurrence - in database or in application code ????

2002-08-20 Thread Darrin Domoney 

One of the features that I am attempting to implement in the system that I
am building is
the capability to schedule events (with recurrence). My question to those of
you that are
more experienced in postgresql is whether you would implement this
functionality in the
database level using triggers or at the application code level (PHP).

Ideally I would like to be able to generate an event off a template
appointment (initial appt)
and have it schedule identical appointments hourly, daily, weekly, monthly,
or by selectable
range (ie: first tuesday every month). I would also like to have the
capability to remove an
appointment and leave others (much like using C pointers - I think)or to
remove all
(cascading delete).

Any suggestions, etc gratefully appreciated.

Darrin



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster