Re: breakage in schema with foreign keys between 7.0.3 and 7.1 - Mailing list 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.