Thread: Referential integrity with primary key spanning multiple columns?

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

On Fri, 2 Aug 2002, Jean-Christian Imbeault wrote:
>
> ...
>
> -- 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 ...

I believe the referenced column must be unique as you suggest.
>
> 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?

You could try using:

 create table REL_GENRES_MOVIES (

 prod_id         integer         references MOVIES,
 major_genre_id  int2            ,
 minor_genre_id  int2            ,

 primary key (prod_id, major_genre_id, minor_genre_id),

 foreign key (major_genre_id, minor_genre_id) references genres(major_genre_id,
minor_genre_id)
 );


although I've never tried it so don't know if that really does what you want
but it looks like it should.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants