Thread: Foreign keys: referencing a REFERENCES doesn7t work?

I have the following schema:

create table PRODUCTS (

id                      char(12)        primary key
);


create table MOVIES (

id                      char(12)        references PRODUCTS,
volume_id               int2            not null default 1,
label_id                integer         references LABELS(id),
length                  int2            not null,

primary key (id, volume_id)
);

create table MOVIE_SERIES (

id                      serial          primary key,
title                   text            not null,
title_kana              text            not null
);

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

Am I missing something or is this a problem with pgsql?

Jc

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

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> 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
> pgsql cannot go up the reference "tree" and follow what MOVIES.id
> references to see that there really is a unique constraint ...

No, there isn't a unique constraint.  Your REFERENCES clause says that
every ID in MOVIES must equal some ID in PRODUCTS; it does *not* say
that two different rows in MOVIES can't reference the same ID in
PRODUCTS.  Add a UNIQUE constraint to MOVIES if that's the behavior you
want.

            regards, tom lane