Почему внешние ключи более используются в теории, чем на практике?

СУБД построены так, чтобы разрешить самое большое количество решений, при этом работая в соответствии с их основными правилами.

Ограничение объединений определенными внешними ключами сильно ограничит функциональность, особенно с учетом большей части разработки не происходит с выделенным администратором баз данных или проверкой SQL / хранимых процедур.

Сказав, что, в зависимости от вашего уровня доступа к данным, вам может потребоваться настроить внешние ключи для использования функциональности. Например, Linq to SQL.

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

Рассмотрим, например, приложение для продаж. Когда кто-то вводит заказ, он, возможно, будет искать клиента в базе данных, чтобы получить адрес или информацию о кредитной карте. Когда он не найдет покупателя, он будет запрограммирован на что-то разумное. Если он подождет, пока попытается вставить строку в таблицу заказов, чтобы обнаружить, что покупателя нет, то обратная связь будет медленнее и менее удобна.

Нет, в принудительном применении нет необходимости; это запретило бы некоторые полезные функции, такие как возможная перегрузка столбцов. Хотя такой вид использования не идеален, он полезен в некоторых реальных ситуациях.

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

Следует отметить, что существенное отсутствие ограничений внешнего ключа в данной схеме является плохим "запахом" и может указывать на некоторые серьезные проблемы проектирования .

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

Эти ограничения применяются разработчиком схемы, и они гарантируют, что данные останутся в модели, которую он предусмотрел. Если ограничений нет, то рано или поздно запрос внесет некоторую несогласованность. Несогласованность приведет к непредсказуемым результатам запросов, и ее очень трудно отменить.

Мне неизвестен диалект SQL, который автоматически неявно объединяет все таблицы внешних ключей. Я видел инструменты генерации кода и словаря данных для составления отчетов, но SQL всегда явный.

Вот почему вы видите, что на практике в SQL ВСЕ соединения являются явными.

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

Что касается того, почему вы можете сделать соединение, которое не соответствует ограничению внешнего ключа между этими таблицами, существует множество примеров. Я считаю, что это часто необходимо, особенно в случае составных ключей с частичным соединением. Мы часто объединяем таблицы, используя частичные версии их первичных ключей в хранилищах данных.

Вас также может заинтересовать эта статья об исключении соединения внешнего ключа оптимизатором.

Внешние ключи не столько связаны с объединениями, сколько с поддержанием целостности базы данных. Доказательством этого является то, что вы можете присоединяться к таблицам любым способом, даже если это необязательно имеет смысл.

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

Потому что на практике теории недостаточно;)

Серьезно, По моему опыту, это происходит главным образом потому, что теория недостаточно гибкая, чтобы учесть все возможности, с которыми вам приходится работать в реальном мире. Только в одном чрезвычайно странном случае, который вы должны сохранить в своей БД (или что-то более распространенное, например, перегрузка столбцов), вам нужно извлечь FK и реализовать его в DAL.

Может быть, вы сможете разработать какое-то решение, которое можно будет заархивировать полностью нормализованным способом (например), но во многих случаях необходимая работа и / или окончательные результаты не стоят того.

Мои два цента.

Вы можете присоединиться к любому выражению. Определяете ли вы внешние ключи в своей базе данных или нет, не имеет значения. Внешние ключи ограничивают INSERT / UPDATE / DELETE, а не SELECT.

Так почему же многие проекты пропускают определение внешних ключей? Есть несколько причин:

• Модель данных плохо спроектирована и требует неработающих ссылок (примеры: полиморфные ассоциации, EAV).

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

• Ограничения мешают некоторым задачам очистки данных. Иногда вам нужно временно разорвать ссылки при рефакторинге данных. Многие СУБД позволяют отключать ограничения, но иногда программисты решают, что проще оставить их отключенными. Если есть частая необходимость в отключении ограничений, это, вероятно, указывает на серьезно нарушенный дизайн базы данных.

Внешние ключи, используемые описанным вами способом, не предназначены для использования. Они предназначены для того, чтобы убедиться, что если запись логически зависит от соответствующей записи, существующей где-то в другом месте, что соответствующая запись действительно существует.

Я считаю, что если у разработчиков / dbas есть время, чтобы любой (A) разработчик назвал свои таблицы и поля или (B) определяют обширные ограничения внешнего ключа, вариант A - простой выбор. Я работал в обеих ситуациях. Там, где для поддержания порядка и удержания людей от ошибок использовались обширные ограничения, это действительно может стать беспорядком.

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

Я думаю, что ограничения внешнего ключа быстро становятся полезными по мере того, как растет количество различных команд, использующих базу данных. Становится трудным обеспечить последовательное именование; знание БД становится фрагментированным; действия db легко могут иметь непредвиденные последствия для другой команды.

Хороший вопрос. Мне всегда было интересно, почему SQL не имеет синтаксиса вроде

SELECT tbl1.col1, tbl2.col2
  FROM tbl1
  JOIN tbl2 USING(FK_tbl1_tbl2)

, где FK_tbl1_tbl2 - это ограничение внешнего ключа между таблицами.

Частично для меня это то, что (и да, это неубедительное оправдание) пользовательский интерфейс в MS SQL Server Management studio для добавления внешних ключей ужасен .

Внешний ключ - это ограничение, согласно которому «любое значение в столбце x таблицы a должно отображаться в столбце y таблицы b», но пользовательский интерфейс для его указания в SSMS не указывает четко, с какой таблицей вы возитесь, которая является родительской таблицей, которая является дочерней таблицей и так далее.

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

Я не могу представить, что нужно объединять таблицы по полям, которые не являются FK. Может ли кто-нибудь привести пример, который имеет смысл?

FOREIGN KEY могут использоваться только для обеспечения ссылочной целостности, если отношения между объектами модели ER отражаются равносвязью между двумя отношениями в реляционной модели.

Это не всегда верно.

Вот пример из статьи в моем блоге, которую я написал некоторое время назад:

• Что такое модель сущности-отношения?

Эта модель описывает товары и диапазоны цен:

А вот реляционная реализация модели:

CREATE TABLE Goods (ID, Name, Price)
CREATE TABLE PriceRange (Price, Bonus)

Как видите, таблица PriceRange имеет только один атрибут, связанный с ценой, Price , но модель имеет два атрибута: StartPrice и EndPrice .

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

Goods
ID  Name               Price
1   Wormy apple        0.09
2   Bangkok durian     9.99
3   Densuke watermelon 999.99
4   White truffle      99999.99

PriceRange
Price   Bonus
0.01       1%
1.00       3%
100.00    10%
10000.00  30%

Мы сохраняем только нижнюю границу каждого диапазона. Верхнюю границу можно легко вывести.

Вот запрос, чтобы найти бонус для каждого товара:

SELECT  *
FROM    Goods
JOIN    PriceRange
ON      PriceRange.Price =
        (
        SELECT  MAX(Price)
        FROM    PriceRange
        WHERE   PriceRange.Price <= Goods.Price
        )

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

Основная причина в том, что в большинстве инструментов MySQL GUI (Navicat, MySQL и т. Д.) Их невозможно настроить без запроса.

Звучит глупо, но я виноват в этом также, поскольку я не запомнил синтаксис: /