pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stef Telford <stef@chronozon.artofdns.com>
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
Message-ID: <01041815073307.00282@devil.hades> (raw)
In-Reply-To: <web-41031@davinci.ethosmedia.com>
References: <web-41031@davinci.ethosmedia.com>

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



view thread (3+ messages)  latest in thread

Message-ID: <01041815073307.00282@devil.hades>
Permalink:  ../01041815073307.00282@devil.hades/
Also on:    postgresql.org/message-id/01041815073307.00282@devil.hades

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stef@chronozon.artofdns.com
  Subject: Re: breakage in schema with foreign keys between 7.0.3 and 7.1
  In-Reply-To: <01041815073307.00282@devil.hades>
  References: <web-41031@davinci.ethosmedia.com> <01041815073307.00282@devil.hades>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox