Foreign key with multiple columns from different tables

Question

Take a stupid example: I have many pets, each of which has a NAME like id and 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 example, I have a CAT named Felix and a dog called Pluto)

In another table, I would like to keep the preferred food for each of my animals:

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

(for example, Felix loves milk, and Pluto loves bones)

As I would like to determine the set of possible preferred products, I store in the third table the types of food for each type of animal:

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

(e.g. DOGs eat bone and meat, CATs eat fish and milk)

Here is my question: I would like to add an external constraint to PREFERED_FOOD, since PREF_FOOD is FOOD_TYPE of FOOD with FOOD.ANIMAL_TYPE = ANIMALS.TYPE. How can I define this foreign key without duplicating ANIMAL_TYPE on PREFERED_FOOD?

I am not a SQL expert, so you can call me stupid if it is very simple :-)

Answer 1

You cannot in SQL. I think you could if SQL supported statements. (Standard SQL-92 specific statements. No one supports them as far as I know.)

To work around this problem, use overlapping restrictions.

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

Answer 2

 FOREIGN KEY (PREF_FOOD) REFERENCES FOOD (FOOD_TYPE) 

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

and in the use of the FOOD table, now it is quite understandable.

 FOREIGN KEY (ANIMAL_TYPE) REFERENCES ANIMALS (ANIMAL_TYPE) 

Answer 3

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

Something like that:

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

Answer 4

Honestly, I had some problems following your requirements, but a simple model for representing animals and their food would probably look like this:

SPECIES_FOOD lists all the foods that this species can eat, and INDIVIDUAL then selects one of them in the PREFERRED_FOOD_NAME field.

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

This, of course, suggests that a single animal cannot have more than one preferred diet. ¹ He also suggests that he may not have a single one - if this is not the case, just do INDIVIDUAL.PREFERRED_FOOD_NAME NOT NULL.

The user INDIVIDUAL_NAME was intentionally not made a key, so you can have, say, two cats named "Felix". If this is not desired, you can easily add the appropriate key.

If all you need to know about food is its name, and you do not need to present food regardless of any species, the FOOD table can be omitted altogether.

---

¹ In the event that there can be several preferred foods per animal, you will need another table "between" INDIVIDUAL "and" SPECIES_FOOD "and be careful to continue using the identifying relationship, so SPECIES_NAME is transferred (to prevent the preference of food that is not edible by mind).

Answer 5

If you take (NATURAL) ANIMAL AND PREFERRED FOOD ADDITION, then you get a table that lists for each animal its type and its preferred food.

You want this combination to be “valid” for each individual animal, where “valid” means “appear on the list of valid animal / food type combinations listed in the PRODUCTS section.

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

The ASSERTION version is the simplest. This is a limitation (I was somewhat liberal with attribute names in order to avoid simply renaming attributes 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 will not support ASSERTION. Therefore, you should use CHECK restrictions. For example, for the PREF_FOOD table, the CHECK constraint you need might look something like this:

 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 be sufficient to enforce your constraint, but then your average SQL engine will not support this CHECK constraint again due to references to tables other than the constraint defined.

Thus, you have options to resort to rather complex (*) settings, such as catcall, or to force a restriction using triggers (and you will have to write quite a lot (at least three or six) thought about this in detail), and your the next best option is to provide this in the application code, and once again there will be three or six (more or less) different places where the same number of different checks should be implemented.

In all of these three scenarios, you will most likely want to document the existence of the constraint, and what exactly is being discussed, in some other place. None of the three will become very obvious to a third party reading this design what this means.

(*) "complex" may not be the right word, but note that such solutions rely on intentional redundancy , so it intentionally goes below 3NF with the design . This means that your design undergoes anomaly updates, which means that it will be more difficult for the user to update the database and maintain its consistency (precisely because of deliberate redundancy).