breakage in schema with foreign keys between 7.0.3 and 7.1
| От | Stef Telford |
|---|---|
| Тема | breakage in schema with foreign keys between 7.0.3 and 7.1 |
| Дата | |
| Msg-id | 01041815073307.00282@devil.hades обсуждение исходный текст |
| Ответ на | Re: Re: Same question about PostgreSql ("Josh Berkus" <josh@agliodbs.com>) |
| Ответы |
Re: breakage in schema with foreign keys between 7.0.3 and
7.1
|
| Список | 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
onclient_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(),
PRIMARYKEY (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
В списке pgsql-sql по дате отправления: