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

Kenward

The first thing I would do is use numeric Keys instead of char keys – Ground

I agree, numeric keys are really better, they simplify things, though many beginners think they are really unnecessary. – Arachne

Can you have a dog named Felix as well as a cat named Felix? Can you have a cat named Pluto as well as a dog named Pluto? This is driving at 'What is the Primary Key of the Animals table?' — it is always helpful to be explicit because (as you can tell from my question) what is obvious to you may not be obvious to other people. The FK in the Preferred Food table suggests you can't have two different pets with the same name at the same time, but that is an indirect — but probably reliable — inference. – Bruce

You could also use a db trigger to enforce data integrity constraints. Also ditch the char foreign keys, just create a tag table to store the descriptions and use ids to look em up – Mansfield

Yeap, sure, the pet name as primary key is not nice at all. I just wrote it this way to illustrate the question. The question was only on the last point, and these tables are just a stupid example ;-). – Kenward

(and I keep the trigger suggestion in mind, you are right, I just wanted to keep on foreign keys, but if I can't triggers could almost do the job) – Kenward

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

Desalvo

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 ! – Kenward

@Kenward Do you really need multiple favorite foods per animal? – Redden

@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. – Shamblin

@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. – Redden

@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]." – Shamblin

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

Answer 2

FOREIGN KEY (PREF_FOOD) REFERENCES FOOD (FOOD_TYPE)

in the PREFERRED_FOOD table, this will make sure that every PREFFOOD in the PREFERRED_FOOD table is already present in the FOOD_TYPE of FOOD table.

and in the FOOD table use, its quite self-explanatory now.

FOREIGN KEY (ANIMAL_TYPE) REFERENCES ANIMALS (ANIMAL_TYPE)

Arachne

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.) – Shamblin

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)' ... – Scant

Answer 3

Depending on what DBMS you are using (please edit your question to include this), you would probably want to create a unique constraint on the ANIMAL_TYPE and PREFERED_FOOD columns.

Something like this:

ALTER TABLE PREFERED_FOOD
ADD CONSTRAINT uc_FoodAnimal UNIQUE (ANIMAL_TYPE,PREFERED_FOOD)

Ground

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) – Kenward

Answer 4

Frankly, I had some trouble following your requirements, but a straightforward model for representing animals and their food would probably look like this:

The SPECIES_FOOD lists all foods a given species can eat, and the INDIVIDUAL then just picks one of them through the PREFERRED_FOOD_NAME field.

Since INDIVIDUAL.SPECIES_NAME is a FK towards both SPECIES and SPECIES_FOOD, an individual can never prefer a food that is not edible by its species.

This of course assumes an individual animal cannot have more than one preferred food.¹ It also assumes it can have none - if that's not the case, just make the INDIVIDUAL.PREFERRED_FOOD_NAME NOT NULL.

The INDIVIDUAL_NAME was intentionally not made a key, so you can have, say, two cats with the name "Felix". If that's not desirable, you'll easy add the appropriate key.

If all you need to know about the food is its name, and you don't need to represent a food independently from any species, the FOOD table can be omitted altogether.

---

¹ In case there can be multiple preferred foods per individual animal, you'd need one more table "between" INDIVIDUAL and SPECIES_FOOD, and be careful to keep using identifying relationships, so SPECIES_NAME is migrated all the way down (to prevent preferring a food not edible by the species).

Redden

Answer 5

If you take the (natural) JOIN of ANIMALS and PREFERRED_FOOD, then you get a table in which for each animal, its type and its preferred food are listed.

You want that combination to be "valid" for each individual animal where "valid" means "to appear in the enumeration of valid animal type/food type combinations that are listed in FOOD.

So you have a constraint that is somewhat similar to an FK, but this time the "foreign key" appears not in a base table, but in a join of two tables. For this type of constraint, the SQL language has CHECK constraints and ASSERTIONS.

The ASSERTION version is the simplest. It is a constraint like (I've been somewhat liberal with the attribute names in order to avoid mere attribute renames that obfuscate the point)

CREATE ASSERTION <name for your constraint here>
 CHECK NOT EXISTS (SELECT ANIMAL_TYPE, FOOD_TYPE
                     FROM ANIMALS NATURAL JOIN PREF_FOOD
                    WHERE (ANIMAL_TYPE, FOOD_TYPE) NOT IN
                          SELECT ANIMAL_TYPE, FOOD_TYPE FROM FOOD_TYPE);

But your average SQL engine won't support ASSERTIONs. So you have to use CHECK constraints. For the PREF_FOOD table, for example, the CHECK constraint you need might look something like

CHECK EXISTS (SELECT 1
                FROM FOOD NATURAL JOIN ANIMAL
               WHERE ANIMAL_TYPE = <animal type of inserted row> AND
                     FOOD_TYPE = <food type of inserted row>);

In theory, this should suffice to enforce your constraint, but then again your average SQL engine will once again not support this kind of CHECK constraint, because of the references to tables other than the one the constraint is defined on.

So the options you have is to resort to rather complex (*) setups like catcall's, or enforcing the constraint using triggers (and you'll have to write quite a lot of them (three or six at least, haven't thought this through in detail), and your next best option is to enforce this in application code, and once again there will be three or six (more or less) distinct places where the same number of distinct checks need to be implemented.

In all of these three scenario's, you will preferably want to document the existence of the constraint, and what exactly it is about, in some other place. None of the three will make it very obvious to a third party reading this design what the heck this is all about.

(*) "complex" might not exactly be the right word, but note that such solutions rely on deliberate redundancy, thus deliberately going below 3NF with the design. And this means that your design is exposed to update anomalies, meaning that it will be harder for the user to update the database AND keep it consistent (precisely because of the deliberate redundancies).

Scant