Foreign keys: referencing a REFERENCES doesn7t work?

From: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Foreign keys: referencing a REFERENCES doesn7t work?
Date: 2002-08-05 08:54:23
Message-ID: 3D4E3D3F.50306@mega-bucks.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2002-08-05 08:59:15 Re: Installing postgresql
Previous Message jeff 2002-08-05 08:07:51 Installing postgresql