Foreign key with multiple columns from different tables

Question

Let's take a stupid example : I have many domestic animals, each one with a NAME as an id and a type (being CAT or DOG), let's write it this way (pseudo code) :

TABLE ANIMALS (
  NAME char,
  ANIMAL_TYPE char {'DOG', 'CAT'}
  PRIMARY KEY(NAME)
)

(for instance, I have a CAT named Felix, and a dog called Pluto)

In another table, I'd like to store the prefered food for each one of my animals :

TABLE PREFERED_FOOD (
  ANIMAL_NAME char,
  PREF_FOOD char
  FOREIGN KEY (ANIMAL_NAME) REFERENCES ANIMALS(NAME)
)

(for instance, Felix likes milk, and Pluto likes bones)

As I would like to define a set of possible prefered foods, I store in a third table the food types, for each type of animal :

TABLE FOOD (
  ANIMAL_TYPE char {'DOG', 'CAT'},
  FOOD_TYPE char
)

(for instance, DOGs eat bones and meat, CATs eat fish and milk)

Here comes my question : I'd like to add a foreign constraint in PREFERED_FOOD, so as the PREF_FOOD is a FOOD_TYPE from FOOD with FOOD.ANIMAL_TYPE=ANIMALS.TYPE. How can I define this foreign key without duplicating the ANIMAL_TYPE on PREFERED_FOOD ?

I'm not an expert with SQL, so you can call me stupid if it is really easy ;-)

Answer 1

You can't in SQL. I think you could if SQL supported assertions. (The SQL-92 standard defined assertions. Nobody supports them yet, as far as I know.)

To work around that problem, use overlapping constraints.

-- Nothing special here.
create table animal_types (
  animal_type varchar(15) primary key
);

create table animals (
  name varchar(15) primary key,
  animal_type varchar(15) not null references animal_types (animal_type),
  -- This constraint lets us work around SQL's lack of assertions in this case.
  unique (name, animal_type)
);

-- Nothing special here.
create table animal_food_types (
  animal_type varchar(15) not null references animal_types (animal_type),
  food_type varchar(15) not null,
  primary key (animal_type, food_type)
);

-- Overlapping foreign key constraints.
create table animals_preferred_food (
  animal_name varchar(15) not null,
  -- This column is necessary to implement your requirement. 
  animal_type varchar(15) not null,
  pref_food varchar(10) not null,
  primary key (animal_name, pref_food),
  -- This foreign key constraint requires a unique constraint on these
  -- two columns in "animals".
  foreign key (animal_name, animal_type) 
    references animals (animal_name, animal_type),
  -- Since the animal_type column is now in this table, this constraint
  -- is simple.
  foreign key (animal_type, pref_food) 
    references animal_food_types (animal_type, food_type)
);