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

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