| 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: | Whole Thread | Raw Message | 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
| 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 |