pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbreakage 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