breakage in schema with foreign keys between 7.0.3 and 7.1

From: Stef Telford <stef(at)chronozon(dot)artofdns(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: breakage in schema with foreign keys between 7.0.3 and 7.1
Date: 2001-04-18 19:07:33
Message-ID: 01041815073307.00282@devil.hades
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello everyone

me again (apologies in advance :). I have been running a database
under 7.0.3 for some months now, and it was all fine. The tables all loaded
and it was working flawlessly. Then 7.1 came out and I noticed it had outer
joins (which are a big win in one of the main views i use).

So, i started loading in the schema into 7.1, but it seems to break.
Now, i have included the 3 tables below, but first i would like to tell some
of the design criteria behind this.

1) I need to have order_id as a primary key across the system (system key ?)
so that i can pull out based on an order_id. The same goes for history_id
in the client.

2) I also need to have the client_id as a secondary key across the system,
as another application frontend references on client_id. its icky but it
works.

3) i have taken out some of the non-important fields, so please dont tell
me that i have over-normalised my data ;p

for some reason though, under 7.1 when trying to get the tables i
get this error -> UNIQUE constraint matching given keys for referenced table
"client" not found. I know what it is saying, but i dont quite understand what
has changed between 7.0.3 and 7.1

CREATE TABLE action
(
ORDER_ID integer PRIMARY KEY,
ORDERTYPE integer NOT NULL,
client_id char(16) NOT NULL,
priority integer DEFAULT 5 NOT NULL,
creation_id name default user,
creation_date datetime default now(),
close_id name NULL,
close_date datetime NULL,
lock_id name NULL,
lock_date datetime NULL
) \g

CREATE TABLE client
(
ORDER_ID integer REFERENCES action
(ORDER_ID)
ON UPDATE CASCADE
INITIALLY DEFERRED,
history_id SERIAL,
active boolean,
client_id char(16) NOT NULL,
change_id name DEFAULT USER,
change_date datetime DEFAULT NOW(),
PRIMARY KEY (ORDER_ID,history_id)
) \g

CREATE TABLE client_dates
(
ORDER_ID integer REFERENCES action
(ORDER_ID)
ON UPDATE CASCADE
INITIALLY DEFERRED,
LOCATION_ID integer NOT NULL,
history_id integer REFERENCES client
(history_id)
ON UPDATE CASCADE
INITIALLY DEFERRED,
active boolean,
client_id char(16) REFERENCES client
(client_id)
ON UPDATE CASCADE
INITIALLY DEFERRED,
dte_action integer NULL,
change_id name DEFAULT USER,
change_date datetime DEFAULT NOW(),
PRIMARY KEY (ORDER_ID,LOCATION_ID,history_id)
) \g

thank you, i know its something almost smackingly obvious but
i cant seem to understand why it was working and now isnt. i even went
through the changelog!

regards
Stef

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Momjian 2001-04-18 19:41:46 Re: RTREE on pointsy
Previous Message Clayton Cottingham aka drfrog 2001-04-18 19:00:24 Re: any proper benchmark scripts?