From: | Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Referential integrity with primary key spanning multiple columns? |
Date: | 2002-08-02 08:14:58 |
Message-ID: | 3D4A3F82.6090502@mega-bucks.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to build the following tables but keep getting an error. Can
anyone point me in the right direction?
-- MOVIES
create table MOVIES (
prod_id integer references PRODUCTS primary key,
volume_id int2 not null default 1,
label_id integer references LABELS,
length time (0)
);
-- GENRES
create table GENRES (
major_genre_id int2 not null,
minor_genre_id int2 not null,
genre_desc text not null,
primary key (major_genre_id, minor_genre_id)
);
-- REL_GENRES_MOVIES
create table REL_GENRES_MOVIES (
prod_id integer references MOVIES,
major_genre_id int2 references GENRES(major_genre_id),
minor_genre_id int2 references GENRES(minor_genre_id),
primary key (prod_id, major_genre_id, minor_genre_id)
);
(The error is for this last table REL_GENRES_MOVIES)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'rel_genres_movies_pkey' for table 'rel_genres_movies'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: UNIQUE constraint matching given keys for referenced table
"genres" not found
Does this mean that I cannot have a foreign key unless it is a UNIQUE
field by itself? I was hoping that since major_genre and minor_genre
together are unique I could use them as foreign keys ...
If the problem is that neither is unique by itself can anyone recommend
some other way where I can use referential integrity with those two fields?
Thanks!
Jc
From | Date | Subject | |
---|---|---|---|
Next Message | Xavier Bugaud | 2002-08-02 08:18:36 | Re: very slow updates |
Previous Message | Ben Liblit | 2002-08-02 08:08:56 | Re: huge performance penalty from constraint triggers |