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