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

Давайте возьмем глупый пример: у меня есть много домашних животных, у каждого из которых есть ИМЯ в качестве идентификатора и типа (будучи CAT или DOG), давайте напишем это так (псевдокод):

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

(например, у меня есть кошка по имени Феликс и собака по имени Плутон)

В другой таблице я хотел бы хранить предпочтительную еду для каждого из моих животных:

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

(например, Феликс любит молоко, а Плутон любит кости)

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

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

(например, СОБАКИ едят кости и мясо, КОШКИ едят рыбу и молоко)

Вот мой вопрос: я хотел бы добавить внешнее ограничение в PREFERED_FOOD, так как PREF_FOOD - это FOOD_TYPE от FOOD с FOOD.ANIMAL_TYPE=ANIMALS.TYPE. Как я могу определить этот внешний ключ без дублирования ANIMAL_TYPE для PREFERED_FOOD?

Я не специалист по SQL, поэтому вы можете назвать меня глупым, если это действительно легко;-)

Источник

5 ответов

Решение

Вы не можете в 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)
);
Источник

Если вы возьмете (натуральное) СОЕДИНЕНИЕ ЖИВОТНЫХ и 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 не будет поддерживать ASSERTIONs. Таким образом, вы должны использовать ограничения 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 при проектировании. А это означает, что ваш дизайн подвержен аномалиям обновления, а это означает, что пользователю будет сложнее обновлять базу данных и поддерживать ее согласованность (именно из-за преднамеренного увольнения).

Источник

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

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

ALTER TABLE PREFERED_FOOD
ADD CONSTRAINT uc_FoodAnimal UNIQUE (ANIMAL_TYPE,PREFERED_FOOD)
Источник 
FOREIGN KEY (PREF_FOOD) REFERENCES FOOD (FOOD_TYPE)

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

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

FOREIGN KEY (ANIMAL_TYPE) REFERENCES ANIMALS (ANIMAL_TYPE)
Источник

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

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

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

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

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

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

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

Источник
Другие вопросы по тегам