Внешний ключ Mysql должен ссылаться на весь первичный ключ родительской таблицы?

Я работаю над небольшим сайтом доставки пиццы и столкнулся с небольшой проблемой с таблицами MySQL.

Я нашел это в Stackoverflow: https://stackoverflow.com/a/10322293/80907

В нем упоминается следующее:

  • Обе таблицы должны использовать механизм INNODB.
  • «В таблице ссылок должен быть индекс, в котором столбцы внешнего ключа перечислены как первые столбцы в том же порядке».

Первое на самом деле не проблема, но второе правило — это то, где я чешу затылок.

Это сайт, на котором можно заказать пиццу, поэтому я сохраняю все данные о пользователях и их заказах в базе данных.

Вот скриншот того, что я собираюсь написать: введите здесь описание изображения

Итак, у меня будет таблица «Пользователи» и таблица «Заказы». Пользователи должны будут иметь отношение «один ко многим» к заказам. Проще говоря, заказ идентифицируется создавшим его пользователем. Так что это один ко многим, идентификация.

Это означает, что таблица «Заказы» будет иметь внешний ключ, такой как «Users_id».

Проблема возникает, когда вам нужно создать таблицу отношения «многие ко многим» между таблицами «Пицца» и «Заказы».

Эта таблица, назовем ее "Order_Details" (MySQL Workbench автоматически назвал ее "Orders_has_Pizzas"), должна ссылаться как на "Orders", так и на "Pizzas".

Теперь, поскольку «Заказы» уже ссылаются на таблицу «Пользователи» в идентифицирующем отношении, это часть первичного ключа «Заказы».

И давайте еще раз выясним это правило:

  • «В таблице ссылок должен быть индекс, в котором столбцы внешнего ключа перечислены как первые столбцы в том же порядке».

Это означает, что вы должны ссылаться на весь первичный ключ. Если я удалю ключ «Order_Users_id», я получу ошибку 1005 при попытке создать базу данных.

Мой вопрос просто: есть ли способ обойти это? Потому что сейчас у меня есть этот идентификатор пользователя, упомянутый в 3 разных таблицах.

Или я неправильно это понимаю, и действительно ли это необходимо делать, чтобы не запрашивать разные таблицы для этих данных?

РЕДАКТИРОВАТЬ: люди, кажется, не согласны со мной в отношении идентификации между пользователями и заказами.

Я не понимаю, как можно идентифицировать отдельный заказ, не зная идентификатора пользователя. После того, как заказ сделан, кто-то должен будет доставить пиццу, а это значит, что ему нужно будет знать, куда ее доставить. Эти данные находятся в таблице Users. Таким образом, Users_id является частью идентификатора одного заказа.

Во всяком случае, так я это вижу. Если я ошибаюсь, объясните почему.

РЕДАКТИРОВАТЬ 2: Благодаря a_horse_with_no_name за разъяснение концепции «идентичности» с точки зрения баз данных, теперь я вижу ошибку своей логики. Информацию можно найти в комментариях.


mysql database composite-primary-key foreign-key-relationship
person KdgDev    schedule 20.01.2014    source источник
comment
Users_id не должен быть частью первичного ключа в Orders.   -  person SLaks    schedule 20.01.2014
comment
@SLaks: это идентифицирующие отношения. Это означает, что он должен быть частью первичного ключа. Или не?   -  person KdgDev    schedule 20.01.2014
comment
@slaks прав, KdgDev, как идентифицировать Орден? order id, нет? Тогда в качестве идентификатора ПК необходим только order id.   -  person dani herrera    schedule 20.01.2014
comment
@danihp Это звучит неправильно для меня. Заказ идентифицируется не сам по себе, а пользователем, создавшим заказ. Таким образом, я вижу это как идентифицирующие отношения. Всегда следует задавать вопрос: может ли дочерняя таблица существовать без родительской таблицы. И ответ для этого случая таков, что не может. Орден не может возникнуть без того, чтобы пользователь не создал его.   -  person KdgDev    schedule 20.01.2014
comment
Каждый заказ имеет уникальный идентификатор (предположительно id), поэтому user_id не обязательно должен быть частью PK таблицы orders. Это было бы необходимо только в том случае, если бы orders.id начиналось с 1 для каждого пользователя, например. id=1, user_id=1, id=2, user_id=1, id=1, user_id=2, ...   -  person a_horse_with_no_name    schedule 20.01.2014
comment
Что касается вашего последнего редактирования: если orders.id является уникальным, этого достаточно, чтобы идентифицировать заказ. Пользователь, которому должен быть доставлен конкретный заказ, является атрибутом заказа, а не идентифицирующим ключом. Тот факт, что вы хотите ссылаться только на orders.id в таблице orders_has_pizza, доказывает, что orders.id уникален в вашей системе.   -  person a_horse_with_no_name    schedule 20.01.2014
comment
@a_horse_with_no_name Итак, означает ли это, что концепция идентичности с точки зрения баз данных sql не является вопросом существования записи в этой таблице из-за записи в другой таблице? выделить одну запись в таблице и убедиться, что она уникальна? Возможность однозначно идентифицировать его.   -  person KdgDev    schedule 20.01.2014
comment
да. Первичный ключ предназначен для уникальной идентификации строки в таблице.   -  person a_horse_with_no_name    schedule 20.01.2014
comment
Сохраняйте имена таблиц во множественном числе, а столбцы в единственном числе. Итак, pizza_id (а не pizzas_id). Также я бы оставил все в нижнем регистре. Особенно с mysql, это может вызвать проблемы с файловыми системами, чувствительными к регистру.   -  person Roger    schedule 20.01.2014
comment
Кроме того, нет реальной причины денормализации (т.е. повторения данных) user_id в orders_has_pizzas, потому что вы получаете пользователя через orders. Вы могли бы денормализировать только в том случае, если у вас возникли проблемы с производительностью и вам нужно было ускорить определенные запросы. Таким образом, orders_has_pizzas (или еще лучше pizza_orders) должны состоять только из order_id и pizza_id.   -  person Raad    schedule 20.01.2014
comment
@Rogier Workbench сделал это автоматически. Я просто не редактировал его перед экспортом в виде изображения PNG. Но вы правы, так и должно быть.   -  person KdgDev    schedule 20.01.2014

Ответы (1)


arrow_upward
2
arrow_downward

Чтобы ответить на ваш первоначальный вопрос, нет, ограничения внешнего ключа InnoDB не требуются для ссылки на весь первичный ключ ссылочной таблицы.

Другими словами, обе следующие работы работают в InnoDB:

mysql> ALTER TABLE Orders_has_Pizzas ADD FOREIGN KEY (Orders_id) 
    REFERENCES Orders (id);
Query OK, 0 rows affected (0.63 sec)

mysql> ALTER TABLE Orders_has_Pizzas ADD FOREIGN KEY (Orders_id,Orders_Users_id) 
    REFERENCES Orders (id, Users_id);
Query OK, 0 rows affected (0.02 sec)

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

mysql> CREATE TABLE Foo (fooid INT PRIMARY KEY, nonunique INT, KEY (nonunique));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE Bar (barid INT PRIMARY KEY, foo_nonid INT, FOREIGN KEY (foo_nonid) 
    REFERENCES Foo(nonunique));
Query OK, 0 rows affected (0.06 sec)

Однако это не стандартный SQL, и я не рекомендую этого делать. Это означает, что строка в Bar может ссылаться более чем на одну строку в родительской таблице Foo. Это означает, что СОЕДИНЕНИЕ между этими двумя отношениями внешнего первичного ключа может неожиданно создать своего рода мини-декартовский продукт.

В таблице Orders любой столбец составного первичного ключа может содержать дубликаты. Это означает, что данная строка в Orders_has_Pizzas теоретически может ссылаться на несколько заказов.

Что касается вопроса об идентифицирующих отношениях, я согласен с тем, что Заказы имеют идентифицирующие отношения по отношению к Пользователям. То есть нет смысла существовать заказу без указанного пользователя.

Но в таблице, где мы используем механизм автоматического увеличения для создания уникальных идентификаторов, добавление дополнительного столбца в PK кажется излишним и ненужным. Зачем нам нужны заказы, чтобы содержать идентификатор пользователя? Один только идентификатор гарантированно уникален и, следовательно, достаточен для уникальной адресации каждой строки.

Я бы сказал, что это практический выбор, тогда как теория поможет нам создать составной первичный ключ в Orders.

Это становится более понятным в таблице «многие ко многим», такой как ваш Orders_has_Pizzas. Эта таблица имеет идентифицирующую связь как с Orders, так и с Pizzas. Первичный ключ состоит из двух внешних ключей, один из которых ссылается на заказы, а другой — на пиццу. Нет необходимости в автоинкрементном ПК вообще.

Некоторые люди добавляют лишний идентификатор автоинкремента для таблиц «многие ко многим» ради соглашения о том, что каждая таблица должна иметь автоматически сгенерированный первичный ключ из одного столбца. Но нет никакой теоретической или практической причины для этого.

CREATE TABLE Orders_has_Pizzas (
  id INT AUTO_INCREMENT PRIMARY KEY, -- what is this column for?
  Orders_id INT,
  Orders_Users_id INT,
  Pizzas_id INT,
);
person Bill Karwin    schedule 20.01.2014
comment
Спасибо за объяснение. Перед тем, как пойти домой, я показал ветку своему инструктору, и она сказала, что мое первоначальное мнение о том, что такое концепция идентичности с точки зрения баз данных, было ошибочным на теоретическом уровне. Прочитав комментарии здесь, я пришел к выводу, что отношения определяют, требуется ли поле для получения уникальной строки. Таким образом, Пользователи и Заказы не имеют идентифицирующих отношений. Мне не нужен user_id, чтобы получить уникальную строку в таблице Orders. Но наличие user_id позволит мне выполнить JOIN на его основе, что приятно иметь в наличии. Правильный? - person KdgDev; 21.01.2014
comment
Из того, что я прочитал, если FK дочерней таблицы является частью ее собственного PK, то это идентифицирующая связь, в противном случае - нет. Хотя это похоже на круговое определение или на то, что реализация ставится перед логическими отношениями, которые она пытается смоделировать. Могут быть случаи (например, ваш), когда FK не является частью PK, но объект Orders действительно не должен опускать ссылку на пользователя. В этом случае вы можете сделать user_id НЕ NULL. - person Bill Karwin; 21.01.2014