Inserting a foreign key with multiple values

Question

I was wondering, is there any possibility to create a table where i have a table which accepts a foreign key but may have multiple values for the same row.

For example:

Employee(id,name,skillid);
Skill(Skillid,skillname);

here an instance for Employee could be

Employee(311,"john", (01,02) );
Skill (01,Java); Skill (02,C++);

I implemented the same making primary key for the table as (is,skillid)

But in a case i have a table :

create table Movie (Movie_ID varchar(5),                              
                            Cast varchar(5),
                            foreign key(Cast) references Person(Person_ID), 
                            primary key(movie_id, Cast));

and another table :

create table Awards  (Award_Id varchar(5),
                                Person_Id varchar(5), 
                                Movie_Id varchar(5),
                                Award_Name  varchar(30),
                                Year number(4),
                                Organization_Id varchar(5),
                                primary key (Award_id,year,Organization_Id),
                                foreign key(Person_Id) references Person(Person_ID),
--                                foreign key(Movie_ID) references Movie(Movie_ID),
                                foreign key(Organization_Id) references Organization(Organization_Id));

In this case i am not able to use Movie_ID as a Foreign key since the table it is referencing has combination of 2 things as its primary key. And i am not using 2nd of those attribute in the Awards table.

Any hints how this can be implemented ?

Answer 1

Unfortunately you've just asked the age old question;

How do I put two things in one column?

The answer is, of course, that you don't; you have two columns. To extend your employee example your employee table would become:

create table employees (
   id number
 , name varchar2(4000)
 , skill_1 number
 , skill_2 number
 , constraint employee_pk primary key (id)
 , constraint employee_skill_1_fs 
      foreign key ( skill_1 ) 
   references skills(skillid)
 , constraint employee_skill_2_fs 
      foreign key ( skill_2 ) 
   references skills(skillid) 
   );

As you can see this isn't a particularly pretty thing to do and breaks normalisation; what happens if you want your employee ( or your employee wants of course ) to have 3 skills? Or 10?.

It would probably be better to create a third table and do all your joins by a single primary and foreign key; so you would have

employees ( employee_id, ..., pk employee_id);
employee_skills ( employee_id, skill_id, ..., pk employee_id, skill_id, fk skill_id );
skills ( skill_id, description, ..., pk skill_id );

Answer 2

If you want to create a composite primary key in the parent table, you would need to create a composite foreign key in the child table. In other words, if you want the parent table to use a natural primary key comprised of three different columns

CREATE TABLE car_type (
  make  VARCHAR2(100),
  model VARCHAR2(100),
  year  NUMBER,
  PRIMARY KEY pk_car_type( make, model, year )
);

then the child table would need to have all three columns as well

CREATE TABLE car (
  vin   VARCHAR2(17) PRIMARY KEY,
  make  VARCHAR2(100),
  model VARCHAR2(100),
  year  NUMBER,
  FOREIGN KEY fk_car_car_type( make, model, year )
    REFERENCES car_type( make, model, year )
);

Using multiple columns in your foreign key definitions generally gets painful as schemas get larger and you need to join more tables together which is why people introduce synthetic primary keys (i.e. meaningless keys that get populated by a sequence). That lets you simplify things

CREATE TABLE car_type (
  car_type_id NUMBER PRIMARY KEY,
  make        VARCHAR2(100),
  model       VARCHAR2(100),
  year        NUMBER,
  UNIQUE uk_car_type( make, model, year )
);

CREATE TABLE car (
  vin   VARCHAR2(17) PRIMARY KEY,
  car_type_id NUMBER REFERENCES car_type( car_type_id )
);

Answer 3

If (movie,cast) is the primary key of the parent table any referencing table has to include both columns in the foreign key. That's just the rules.

There are two ways to resolve this. Either you have got the primary key wrong, in which case you need to amend the constraint on the MOVIES table. Alternatively you need to add the CAST column to the AWARDS table.

Composite keys are a pain in the neck when it comes to foreign keys. This is why many practitioners prefer to have a surrogate (or synthetic) primary key, so that the child tables only have to reference a single column. The original - composite - key is still enforced, but as a unique key.