Re: breakage in schema with foreign keys between 7.0.3 and 7.1

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.