Внешний ключ с несколькими столбцами из разных таблиц

Вы не можете использовать SQL. Я думаю, вы могли бы, если бы SQL поддерживал утверждения. (Утверждения определены стандартом SQL-92. Насколько мне известно, их пока никто не поддерживает.)

Чтобы обойти эту проблему, используйте перекрывающиеся ограничения.

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

FOREIGN KEY (PREF_FOOD) REFERENCES FOOD (FOOD_TYPE)

в таблице PREFERRED_FOOD это гарантирует, что каждый PREFFOOD в таблице PREFERRED_FOOD уже присутствует в таблице FOOD_TYPE таблицы FOOD.

и в использовании таблицы FOOD теперь это говорит само за себя.

FOREIGN KEY (ANIMAL_TYPE) REFERENCES ANIMALS (ANIMAL_TYPE)

В зависимости от того, какую СУБД вы используете (отредактируйте свой вопрос, включив это), вы, вероятно, захотите создать уникальное ограничение для столбцов ANIMAL_TYPE и PREFERED_FOOD.

Что-то вроде этого:

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

Честно говоря, у меня были некоторые проблемы с соблюдением ваших требований, но простая модель для представления животных и их еды, вероятно, будет выглядеть так:

SPECIES_FOOD перечисляет все продукты, которые может съесть данный вид, а затем INDIVIDUAL просто выбирает одну из них в поле PREFERRED_FOOD_NAME.

Поскольку INDIVIDUAL.SPECIES_NAME относится к как SPECIES, так и SPECIES_FOOD, человек никогда не может предпочесть пищу, несъедобную для его вида.

Это, конечно, предполагает, что у отдельного животного не может быть более одной предпочтительной пищи. ¹ Также предполагается, что у него не может быть ни одного - если это не так, просто сделайте INDIVIDUAL.PREFERRED_FOOD_NAME NOT NULL.

INDIVIDUAL_NAME был намеренно не сделан ключом, поэтому у вас могут быть, скажем, две кошки с именем "Феликс". Если это нежелательно, вы легко добавите соответствующий ключ.

Если все, что вам нужно знать о продукте - это его название, и вам не нужно представлять продукт независимо от какого-либо вида, таблицу FOOD можно вообще опустить.

¹ В случае, если для одного животного может быть несколько предпочтительных продуктов, вам понадобится еще одна таблица "между" INDIVIDUAL и SPECIES_FOOD, и будьте осторожны, чтобы продолжать использовать идентифицирующие отношения, поэтому SPECIES_NAME переносится полностью пух (чтобы не отдавать предпочтение еде, несъедобной для данного вида животных).

Если вы возьмете (естественное) СОЕДИНЕНИЕ ЖИВОТНЫХ и PREFERRED_FOOD, вы получите таблицу, в которой для каждого животного указаны его тип и предпочтительная пища.

Вы хотите, чтобы эта комбинация была «действительной» для каждого отдельного животного, где «действительный» означает «появиться в перечне допустимых комбинаций типа животного / типа корма, перечисленных в FOOD.

Итак, у вас есть ограничение, которое несколько похоже на FK, но на этот раз «внешний ключ» появляется не в базовой таблице, а в соединении двух таблиц. Для этого типа ограничения в языке SQL есть ограничения CHECK и ASSERTIONS.

Версия ASSERTION самая простая. Это ограничение вроде (я был несколько либерален с именами атрибутов, чтобы избежать простого переименования атрибутов, которое запутывает точку)

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

Но ваш средний движок SQL не поддерживает утверждения. Поэтому вам нужно использовать ограничения CHECK. Например, для таблицы PREF_FOOD необходимое ограничение CHECK может выглядеть примерно так:

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

Теоретически этого должно быть достаточно для обеспечения соблюдения вашего ограничения, но опять же, ваш средний механизм SQL снова не будет поддерживать такого рода ограничение CHECK из-за ссылок на таблицы, отличные от той, для которой определено ограничение.

Таким образом, у вас есть варианты: прибегнуть к довольно сложным (*) настройкам, таким как catcall, или принудить ограничение с помощью триггеров (и вам придется написать довольно много из них (по крайней мере, три или шесть, не продумали это подробно), и ваш следующий лучший вариант - обеспечить это в коде приложения, и снова будет три или шесть (более или менее) отдельных мест, где необходимо реализовать одинаковое количество отдельных проверок.

Во всех этих трех сценариях желательно задокументировать существование ограничения и его суть в другом месте. Ни один из трех не сделает очевидным для стороннего лица, читающего этот дизайн, о чем, черт возьми, все это идет.

(*) «сложный» может быть не совсем правильным словом, но обратите внимание, что такие решения основаны на преднамеренной избыточности, поэтому намеренно ниже 3NF в дизайне. А это означает, что ваш дизайн подвержен аномалиям обновления, а это означает, что пользователю будет сложнее обновлять базу данных И поддерживать ее согласованность (именно из-за преднамеренного дублирования).