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 ;-)

Solution

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)
);

Comments

[Mike Sherrill 'Cat Recall']

animals.animal_type isn't unique. If it's not unique, you can't use it as the target for a foreign key constraint. (Except in MySQL, where they warn you not to do it in the documentation, but don't stop you from doing it like all the sane dbms do.)

[user1695584]

ok, so I have to add the animal_type in the second table :-( (well, here you put is as part of the key, so it sounds logical, but if you imagine the only key is the pet name, it is a duplication :-( ) Thanks for the answer, I'll have a look to assertions, I don't know about this !

[user1695584]

Yeap, sure too ;-). But the question I had was only on adding a foreign key with multiple columns and constraints coming from different tables ;-) (and it was just a general SQL question, I have no particular DBMS in mind)

[Branko Dimitrijevic]

@user1695584 Do you really need multiple favorite foods per animal?

[Mike Sherrill 'Cat Recall']

@BrankoDimitrijevic: I completely overlooked that point. I'll blame my dog for that. The primary key for that table should probably be just "animal_name". I'll wait for the OP to verify.

[Branko Dimitrijevic]

@Catcall Furthermore, animals_preferred_food table can be completely eliminated in that case. The single favorite food per animal could be represented by a field in the animals table.

[Mike Sherrill 'Cat Recall']

@BrankoDimitrijevic: Part of the point of the question was storing a suitable preference for each type of animal. To do that, you need to store "Food [food_type] is a suitable food for animals of [animal_type]."

[Branko Dimitrijevic]

@Catcall And that's what animal_food_types table is for, right? I was talking about eliminating animals_preferred_food.

[Erwin Smout]

What is "sane" about that restriction ? Courses can only be organized in cities where we have a department. City is not a key for DEPT. Yet, the rule per se is still nothing more than just 'CRS(CITY) REFERENCES DEPT(CITY)' ...