Foreign Keys - Что они делают для меня?
Я создаю небольшое приложение и устанавливаю отношения внешнего ключа между таблицами. Тем не менее, я' я не понимаю, ПОЧЕМУ мне это действительно нужно? В чем преимущество - помогает ли мне при написании запросов не выполнять никаких объединений? Вот пример фрагмента моей базы данных:
+-------------------+
| USERS |
+-------------------+
| user_id |
| username |
| create_date |
+-------------------+
+-------------------+
| PROJECTS |
+-------------------+
| project_id |
| creator |
| name |
| description |
+-------------------+
Существует ключевая взаимосвязь между пользователями . user_id и проектами . создателем
Буду ли я быть в состоянии выполнить запрос, как это?
SELECT * FROM PROJECTS WHERE USERS.username = "реальный пользователь";
Так как MySQL должен знать отношения между таблицами? Если нет, то какова реальная функция внешних ключей в дизайне базы данных?
5 ответов
Внешние ключи обеспечивают ссылочную целостность. Данные в столбце внешнего ключа проверяются - значение может быть только таким, которое уже существует в таблице и столбце, определенном во внешнем ключе. Это очень эффективно для остановки «плохих данных» - кто-то не может вводить все, что хочет - числа, текст ASCII и т. Д. Это означает, что данные нормализованы - повторяющиеся значения были идентифицированы и изолированы в их собственной таблице, поэтому больше нет проблем о работе с чувствительностью к регистру в тексте ... и значения согласованы. Это ведет к следующей части - внешние ключи - это то, что вы используете для объединения таблиц.
Ваш запрос для проектов, имеющихся у пользователя, не будет работать - вы ссылаетесь на столбец из таблицы USERS , когда в запросе нет ссылки на таблицу и не используется подзапрос для получения эту информацию, прежде чем связывать ее с таблицей ПРОЕКТЫ . На самом деле вы бы использовали:
SELECT p.*
FROM PROJECTS p
JOIN USERS u ON u.user_id = p.creator
WHERE u.username = 'John Smith'
Использование ограничения внешнего ключа может обеспечить следующее:
- Предотвратить базу данных, содержащую несогласованные данные, с помощью предотвращение несовпадения ключей
- Предотвращение базы данных, содержащей несогласованные данные, путем автоматического удаления потерянных строк (с помощью ON DELETE CASCADE)
- Служить для документирования будущим разработчикам, какой столбец является внешним ключом для которого
Конечно, это не обязательно делать любой из этих вещей, но, вероятно, со временем улучшит качество кода. Быть строгим в отношении вещей обычно хорошо - это приводит к большему количеству ошибок при тестировании (и, следовательно, меньшему количеству в производстве)
Если каждый пользователь принадлежит ровно одному проекту, а каждый проект принадлежит ровно одному пользователю, то считается, что таблицы имеют связь один к одному, и наличие ключевой связи между users.user_id и projects.project_id является нормальным (хотя, вероятно, нестандартным).
Однако, если пользователь может принадлежать многим проектам (или проект может иметь много пользователей), тогда у вас есть связь один ко многим и вам нужен внешний ключ.
Если проекты могут иметь много пользователей, а пользователи могут принадлежать многим проектам, тогда у вас есть отношения "многие ко многим" и вам нужна более сложная модель.
Если вы никогда не выполняете соединения, вам не нужны внешние ключи.
Если задуматься, если вы никогда не объединяетесь, вам не нужна реляционная база данных! (небольшая шутка) Серьезно, если у вас более одной таблицы, вам лучше узнать, как использовать объединения и как создавать внешние ключи в схеме.
Как сказали предыдущие респонденты, внешние ключи обеспечивают ссылочную целостность. Без ссылочной целостности объединения дают загадочные результаты.
В моем предыдущем ответе не был указан реальный вопрос, стоящий за вопросом. На вопрос, на который я ответил, было «почему в схемах SQL есть внешние ключи», а ответ был «для того, чтобы выполнять соединения».Но, перечитывая вопрос, я понимаю гораздо более тонкий вопрос: «Почему SQL не может выполнять соединения за меня, если он знает связи». Это действительно хороший вопрос. Он заслуживает лучшего ответа, чем приведенный выше.
Возможен язык, на котором движок предоставляет условия соединения. Достаточно взглянуть на графический инструмент создания запросов в Microsoft Access. После того, как вы объявили все взаимосвязанные связи в Access, можно извлечь данные из нескольких таблиц, не задавая заново условия соединения. Access вычисляет их автоматически.
Если создать запрос с двумя таблицами в Access, а затем переключиться на представление SQL, можно будет увидеть, что Access фактически создал соединение с условием соединения. Такая возможность возможна и в символьных языках, но SQL не является таким языком.
Попутно отмечу, что одному пользователю может принадлежать множество проектов. Таким образом, "справочная таблица" в приведенной выше схеме - это пользователи, а не проекты. Я полагаю, что более простым автоматическим направлением навигации будет автоматический поиск из справочной таблицы, а не наоборот.
По сути, они больше не дадут вам функциональности. Они предотвращают любые вставки или обновления, нарушающие ссылочную целостность вашей модели данных.
Еще одним ключевым аспектом, на мой взгляд, является то, что они передают модель данных любым другим разработчикам, использующим ее. Я часто смотрел, какие внешние ключи должны иметь таблицу, чтобы увидеть, как модель данных с первого взгляда согласуется.