pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
breakage in schema with foreign keys between 7.0.3 and 7.1
3+ messages / 2 participants
[nested] [flat]

* breakage in schema with foreign keys between 7.0.3 and 7.1
@ 2001-04-18 19:07  Stef Telford <stef@chronozon.artofdns.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Stef Telford @ 2001-04-18 19:07 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: breakage in schema with foreign keys between 7.0.3 and 7.1
@ 2001-04-18 19:48  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Stef Telford <stef@chronozon.artofdns.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Stephan Szabo @ 2001-04-18 19:48 UTC (permalink / raw)
  To: Stef Telford <stef@chronozon.artofdns.com>; +Cc: pgsql-sql


On Wed, 18 Apr 2001, Stef Telford wrote:

> 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! 

Hmm, don't know why it's not in changelog, but the spec requires that 
the target fields of a foreign key constraint are themselves constrained
by a unique or primary key constraint.  7.0 didn't actually check this,
but 7.1 does.  The reason for this is because while 7.0 would let you
specify such a constraint, it wouldn't really work entirely properly
if the field wasn't actually unique.  You'll need a unique constraint
on client.client_id.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: breakage in schema with foreign keys between 7.0.3 and 7.1
@ 2001-04-18 20:39  Stef Telford <stef@chronozon.artofdns.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Stef Telford @ 2001-04-18 20:39 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

Stephan Szabo wrote:
> Hmm, don't know why it's not in changelog, but the spec requires that
> the target fields of a foreign key constraint are themselves constrained
> by a unique or primary key constraint. 

maybe its time for me to go and re-read the changelog with a fine tooth
comb (it has been known for me to be blind to the obvious before and
if this is the case then i more than apologise :)

> 7.0 didn't actually check this,
> but 7.1 does.  The reason for this is because while 7.0 would let you
> specify such a constraint, it wouldn't really work entirely properly
> if the field wasn't actually unique.  You'll need a unique constraint
> on client.client_id.

hhrrm. the only problem with -that- is that client_id by itself is not 
unique, but in conjunction with order_id it is. order_id is wholly 
unique. maybe i should jst drop the foreign key on client_id then,
although i did want to use referential integrity on the client_id on
an insert. 

although now i think about this, the criteria for having the changes
on client_id cascading are totally gone and i could (read will) jst
use a 'references' column.

in short, thank you, i have jst figured out what an idiot i have been
(again i hear you all say ;)

many thanks and good work on postrgresql 7.1, it seems to be quite
a bit quicker (and praise the lord for outer joins =)

stefs



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-04-18 20:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-18 19:07 breakage in schema with foreign keys between 7.0.3 and 7.1 Stef Telford <stef@chronozon.artofdns.com>
2001-04-18 19:48 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-18 20:39   ` Stef Telford <stef@chronozon.artofdns.com>

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