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  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Subqueries in select clause
Следующее
От: Oleg Bartunov
Дата:
Сообщение: Re: RTREE on points