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

При изучении реляционной теории внешние ключи, конечно, обязательны. Но на практике, в каждом месте, где я работал, табличные продукты и объединения всегда выполняются путем указания ключей явно в запросе, вместо того, чтобы полагаться на внешние ключи в СУБД.

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

Почему вы так думаете? Должна ли СУБД обеспечивать, чтобы соединения и продукты выполнялись только с помощью внешних ключей?

EDIT: Спасибо за все ответы. Теперь мне ясно, что основной причиной для FK является целостность ссылок. Но если вы создаете БД, все отношения в модели (IE стрелки в ERD) становятся внешними ключами, по крайней мере теоретически, независимо от того, определяете ли вы их как таковые в своей СУБД, они семантически FK. Я не могу представить себе необходимость объединения таблиц по полям, которые не являются FK. Может ли кто-нибудь привести пример, который имеет смысл?

PS: Я знаю, что отношения N: M становятся отдельными таблицами, а не внешними ключами, просто опущены для простоты.

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

«Внешний ключ идентифицирует столбец или набор столбцов в одной таблице, который ссылается на столбец или набор столбцов в другой таблице. Значения в одной строке столбцов ссылок должны встречаться в одной строке в ссылочной таблице.

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


RE: Ваш вопрос: «Я не могу представить себе необходимость объединения таблиц по полям, которые не являются FK»:

При определении ограничения внешнего ключа столбец (ы) в ссылочной таблице должен быть основным ключом таблицы ссылок или, по меньшей мере, ключом-кандидатом.

При выполнении объединений нет необходимости в соединении с первичными ключами или ключами-кандидатами.

Ниже приведен пример, который может иметь смысл:

CREATE TABLE clients ( client_id uniqueidentifier NOT NULL, client_name nvarchar(250) NOT NULL, client_country char(2) NOT NULL ); CREATE TABLE suppliers ( supplier_id uniqueidentifier NOT NULL, supplier_name nvarchar(250) NOT NULL, supplier_country char(2) NOT NULL ); 

Затем запросите следующее:

 SELECT client_name, supplier_name, client_country FROM clients INNER JOIN suppliers ON (clients.client_country = suppliers.supplier_country) ORDER BY client_country; 

Другой случай, когда эти объединения имеют смысл, – это базы данных, которые предлагают геопространственные функции, такие как SQL Server 2008 или Postgres с PostGIS. Вы сможете делать такие запросы:

 SELECT state, electorate FROM electorates INNER JOIN postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1); 

Источник: ConceptDev – SQL Server 2008 География: STIntersects, STArea

Вы можете увидеть другой подобный пример геопространственности в принятом ответе на сообщение « Проблема с запросом Sql 2008», которую LatLong существует в полигоне географии? »:

 SELECT G.Name, COUNT(CL.Id) FROM GeoShapes G INNER JOIN CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1 GROUP BY G.Name; 

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

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

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

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

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

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

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

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

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

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

Как вы можете видеть, таблица PriceRange имеет только один атрибут Price , 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, но между этими отношениями не может быть объявлен внешний ключ, поскольку операция, используемая для их привязки, не является эквивалентом.

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

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

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

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

Итак, почему многие проекты пропускают определение внешних ключей? Существует несколько причин:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что-то должно поддерживать целостность базы данных, но делать это внутри самой СУБД не всегда является лучшим способом.

Я программировал пару десятилетий, так как задолго до того, как реляционные базы данных стали нормой. Когда я впервые начал работать с MySQL, когда я научил себя PHP, я увидел вариант Foreign Key, и первая мысль была «Wow! Это отстало». Причина в том, что только дурак считает, что лаборатория диктует реальность. Было сразу видно, что, если вы не кодируете приложение, которое никогда не будет изменено, вы будете обертывать приложение в стальном литье, где единственный вариант – либо построить больше таблиц, либо придумать творческие решения.

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

Единственная причина, по которой я когда-либо находил какие-либо ограничения на таблицу, – это ленивые кодеры. Не желает писать чистый код для проверки целостности данных.

Майкл

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

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

Хороший вопрос. Я всегда задавался вопросом, почему SQL не имеет синтаксиса типа

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

где FK_tbl1_tbl2 – это ограничение внешнего ключа между таблицами. Это было бы невероятно намного более полезным, чем NATURAL JOIN или Oracle USING (col1, col2).

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

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

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

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

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

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

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

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

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

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

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