Received: from chronozon.artofdns.com (cr466046-a.etob1.on.wave.home.com [24.42.158.249]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3IJEUu58774 for ; Wed, 18 Apr 2001 15:14:30 -0400 (EDT) (envelope-from stef@chronozon.artofdns.com) Received: from devil.hades (devil.hades [192.168.2.6]) by chronozon.artofdns.com (Postfix) with SMTP id 83C63C76C for ; Wed, 18 Apr 2001 15:16:59 -0400 (EDT) Content-Type: text/plain; charset="iso-8859-1" From: Stef Telford Organization: Hades Inc To: pgsql-sql@postgresql.org Subject: breakage in schema with foreign keys between 7.0.3 and 7.1 Date: Wed, 18 Apr 2001 15:07:33 -0400 X-Mailer: KMail [version 1.2] References: In-Reply-To: MIME-Version: 1.0 Message-Id: <01041815073307.00282@devil.hades> Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/311 X-Sequence-Number: 2667 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