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."