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

Ответ 1

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

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

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

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 - какие LatLongs существуют в полигоне географии?:

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, которые не имеют ничего общего с внешними ключами и ключами-кандидатами и могут быть полезны на практике.

Ответ 2

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

Ответ 3

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

Ответ 4

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

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

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

Ответ 5

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

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

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

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

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

Ответ 6

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

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

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

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

Ответ 7

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

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

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

Ответ 8

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

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

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

Ответ 9

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

Ответ 10

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

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

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

Ответ 11

Я программировал пару десятилетий, так как задолго до того, как реляционные базы данных стали нормой. Когда я впервые начал работать с MySQL, когда я научил себя PHP, я увидел вариант "Внешний ключ", и первая мысль была "Ого! Это отстало". Причина в том, что только дурак считает, что лаборатория диктует реальность. Было сразу видно, что, если вы не кодируете приложение, которое никогда не будет изменено, вы завершаете приложение в стальном литье, где единственным вариантом является либо создание большего количества таблиц, либо создание творческих решений.

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

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

Майкл

Ответ 12

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

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

Ответ 13

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

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

Ответ 14

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

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

Ответ 15

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

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

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

Ответ 16

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

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

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

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

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

Ответ 17

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