Referential integrity with primary key spanning multiple columns?
От | Jean-Christian Imbeault |
---|---|
Тема | Referential integrity with primary key spanning multiple columns? |
Дата | |
Msg-id | 3D4A3F82.6090502@mega-bucks.co.jp обсуждение исходный текст |
Ответы |
Re: Referential integrity with primary key spanning multiple
|
Список | 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
В списке pgsql-general по дате отправления: