Re: Foreign keys: referencing a REFERENCES doesn7t work?

* Jean-Christian Imbeault wrote on Monday, 2002-08-05:
[I edited out the unnecessary fields]

> create table PRODUCTS (
> id char(12) primary key
> );
>
> create table MOVIES (
> id char(12) references PRODUCTS,
> volume_id int2 not null default 1,
> primary key (id, volume_id)
> );
>
> create table MOVIE_SERIES (
> id serial primary key
> );
>
> create table REL_MOVIES_SERIES (
> prod_id char(12) references MOVIES(id),
> series_id integer references MOVIE_SERIES(id),
> primary key (prod_id, series_id)
> );
>
> When trying to enter this SQL I get the following error:
>
> ERROR: UNIQUE constraint matching given keys for referenced table
> "movies" not found
>
> Seems that pgsql is fine when MOVIES.id references PRODUCTS.id for a
> foreign key but if a table references MOVIES.prod_id for a foreign key
^^^^^^
REL_MOVIES_SERIES?

> pgsql cannot go up the reference "tree" and follow what MOVIES.id
> references to see that there really is a unique constraint ... hence I
> get an error.

There is _no_ unique constraint on MOVIES.id since it is not declared
UNIQUE and is part of a composite primary key.

--
Christian Ullrich Registrierter Linux-User #125183

"Deliver."