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