Нужны ли внешние ключи в дизайне базы данных?

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

Есть ли другие варианты использования внешних ключей? Я что-то здесь упускаю?

20.08.2008 20:18:08
Это часто встречается здесь. Я обвиняю Джоэла Спольски :-). Здесь много хороших ответов; вместо того, чтобы перепечатывать мой, я просто дам вам ссылку: stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys
SquareCog 19.04.2009 15:02:54
«Предположим, программист на самом деле делает это правильно», - я даже не могу себе представить такой сценарий.
recursive 19.04.2009 15:05:38
«Внешний ключ» - это идея, а не технология. Это правило отношений. Ваш вопрос на самом деле о том, следует ли вам пытаться применить правило в своем коде или позволить базе данных помочь вам. Когда задействован параллелизм, лучше всего позволить механизму базы данных применять правило, так как он знает обо ВСЕМ, что происходит в базе данных, в то время как ваш код не может знать об этом.
Triynko 25.08.2009 18:20:01
@lubos & cdeszaq. На самом деле, это Реляционное правило ... это подмножество правила 10 «Двенадцати заповедей» Кодда ... «Независимость целостности», в которой в основном говорится, что реляционная целостность СУБД должна поддерживаться независимо от любого приложения, которое обращается к ней, что это именно то, что я объяснял в простой для понимания форме. Это правило реализуется, помимо прочего, посредством ограничений внешнего ключа. Так что да, идея внешнего ключа - это правило отношений.
Triynko 24.02.2010 22:01:31
@lubos: Чтобы уточнить, вы говорите о том, собираетесь ли вы использовать определенную функцию или нет, но я говорю о том, необходимо ли наличие этой функции, чтобы иметь полноценную, полностью функциональную СУБД. Ссылочные ограничения, если и когда вы решите их использовать, должны применяться в СУБД (а не в приложении), так что это должна быть функция, и в этом смысле это требование реляционной модели, если вы собираетесь разработать полную RDBMS.
Triynko 1.03.2010 18:27:04
24 ОТВЕТА
РЕШЕНИЕ

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

101
20.08.2008 20:19:33
Если вам нужен индекс, создайте его, это не должно быть основной причиной для FK. (На самом деле в определенных обстоятельствах (например, больше вставок, чем выбирает) поддержание FK может выполняться медленнее.)
Robert 21.03.2010 17:28:57
Это ужасный ответ FKs могут добавить дополнительные накладные расходы, но не улучшить производительность.
Agile Jedi 13.02.2015 18:42:34
В SQL-сервере они по умолчанию не индексируются ни рефери, ни реферером. sqlskills.com/blogs/kimberly/...
user420667 27.06.2016 20:26:43
Ни в оракуле; Вы должны создать индексы (для столбцов FK) самостоятельно.
Littlefoot 10.02.2018 20:17:01

Внешние ключи также могут помочь программисту писать меньше кода, используя такие вещи, как ON DELETE CASCADE. Это означает, что если у вас есть одна таблица с пользователями, а другая - с заказами или чем-то еще, удаление пользователя может автоматически удалить все заказы, которые указывают на этого пользователя.

58
18.09.2018 06:47:33
@ Грег Хьюгилл Это может привести к множеству проблем. Вы должны быть очень осторожны с такими мыслями, как DELETE CASCADE, так как во многих случаях вы хотите сохранить заказы, созданные пользователем при удалении пользователя.
Kibbee 20.08.2008 20:28:12
Хотя это, вероятно, следует обрабатывать на уровне бизнес-логики. Решение о том, хранить ли связанные дочерние записи или нет, не совсем то же самое, что гарантировать, что никакие значения не нарушают отношения внешнего ключа.
Codewerks 6.10.2008 21:38:42
Другой проблемой является аудит, если аудит не выполняется на уровне базы данных, каскадные обновления или удаления сделают недействительным ваш журнал аудита.
si618 1.09.2009 04:52:11
@Codewerks: бизнес-логика может быть в БД.
Fantius 1.02.2011 19:31:41

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

Строго говоря, они не обязательны , но выгоды огромны.

Я совершенно уверен, что FogBugz не имеет ограничений внешнего ключа в базе данных. Мне было бы интересно услышать, как команда Fog Creek Software структурирует свой код, чтобы гарантировать, что они никогда не внесут несоответствие.

43
18.09.2018 07:04:58
Джоэл: «До сих пор у нас никогда не было проблем». До сих пор я никогда не ездил на фонарный столб. Но я все еще думаю, что это хорошая идея, чтобы носить ремни безопасности ;-)
Tony Andrews 4.11.2008 10:36:18
Может быть, вы никогда не видели проблему, но, возможно, она есть ... Большинство баз данных используют соглашение, подобное id_xxx, точно такое же, как ixXXX
FerranB 5.02.2009 21:54:59
@Joel: соглашения об именах вместо применения правил? С таким же успехом можно покончить с типом, пока вы на нем.
jcollum 17.09.2009 20:54:16
@Eric: Вы держите Fog Creek как своего рода аватар для разработки программного обеспечения здесь. Если бы вы сказали «У компании в Нью-Йорке нет внешних ключей в их БД ...», мы бы все сказали «А?»
jcollum 17.09.2009 20:56:08
Эрик: FogBugz использует соглашение об именах для внешних ключей. Например, ixBug понимается как индекс первичного ключа таблицы Bug. До сих пор у нас никогда не было проблем. - Джоэл Спольски
Sam Saffron 13.02.2012 01:48:13

Без внешнего ключа как сказать, что две записи в разных таблицах связаны?

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

9
20.08.2008 20:24:15

Вы можете просмотреть внешние ключи как ограничение,

  • Помогите сохранить целостность данных
  • Показать, как данные связаны друг с другом (что может помочь в применении бизнес-логики и правил)
  • При правильном использовании может помочь повысить эффективность извлечения данных из таблиц.
1
10.08.2013 19:27:48

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

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

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

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

6
10.08.2013 19:29:11
Это как лук. ФК - последний уровень защиты. Если это не встроенная локальная база данных, приложения, пытающиеся обеспечить ссылочную целостность, всегда являются плохой идеей.
Fabricio Araujo 4.10.2013 19:23:08

Предположим, программист на самом деле делает это правильно

Делать такое предположение мне кажется крайне плохой идеей; в общем программное обеспечение феноменально глючит.

И в этом все дело. Разработчики не могут сделать все правильно, поэтому обеспечение базы данных плохими данными - это хорошо.

Хотя в идеальном мире естественные объединения будут использовать отношения (то есть ограничения FK), а не сопоставлять имена столбцов. Это сделало бы ФК еще более полезными.

12
20.08.2008 20:35:10
Хорошо, было бы неплохо объединить две таблицы с "ON [Relationship]" или другим ключевым словом и позволить БД выяснить, какие столбцы задействованы. Кажется довольно разумным на самом деле.
jcollum 17.09.2009 20:58:04

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

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

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

Изменить: Я хотел бы добавить, что, IMO, использование внешних ключей для поддержки ON DELETE или ON UPDATE CASCADE не обязательно хорошая вещь. На практике я обнаружил, что каскад при удалении должен быть тщательно рассмотрен на основе взаимосвязи данных - например, у вас есть естественный родительский-дочерний элемент, где это может быть нормально, или связанная таблица представляет собой набор значений поиска. Использование каскадных обновлений подразумевает, что вы разрешаете изменять первичный ключ одной таблицы. В этом случае у меня есть общее философское несогласие в том, что первичный ключ таблицы не должен изменяться. Ключи должны быть по своей природе постоянными.

13
21.08.2008 12:12:54

Да.

  1. Они держат тебя честно
  2. Они держат новых разработчиков честными
  3. Ты можешь сделать ON DELETE CASCADE
  4. Они помогают вам создавать хорошие диаграммы, которые сами объясняют связи между таблицами
21
10.08.2013 19:30:46
что вы подразумеваете под честностью?
dspacejs 6.09.2015 02:33:19
Я думаю, честно с концепцией. Это предотвращает вас от мошенничества с данными, делая быстрое и неэффективное программирование.
Oreste Viron 8.01.2017 21:09:09

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

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

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

  2. Поддержка инструмента. Гораздо проще создавать модели данных с использованием Visual Studio 2008, которые можно использовать для LINQ to SQL, если существуют правильные отношения внешнего ключа.

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

1
10.08.2013 19:32:06
Я работаю на системах, которые их не используют. И я сожалею об этом регулярно. Я видел больше случаев, когда я могу сосчитать бессмысленные данные, которые были бы предотвращены с помощью надлежащих ограничений.
recursive 19.04.2009 15:15:25
И, работая почти шесть месяцев с нашим внешним ключом над нашим текущим проектом, я полностью согласен с этим комментарием.
John Christensen 20.04.2009 13:17:03

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

Отладить ошибку ограничения FK гораздо приятнее, чем восстанавливать удаление, которое сломало ваше приложение.

4
20.08.2008 20:57:14

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

В коде мы обычно просто генерируем исключение где-то - но в SQL мы обычно просто получаем «неправильные» ответы.

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

1
10.08.2013 19:33:00
Ограничения уникальности указывают на высокую мощность, которая используется оптимизатором при выборе механизма соединения.
Peter Wone 22.12.2009 22:14:03

Схема базы данных без ограничений FK похожа на вождение без ремня безопасности.

Однажды вы пожалеете об этом. Не тратить так мало дополнительного времени на основы проектирования и целостность данных - верный способ избежать головной боли позже.

Примете ли вы код в вашем приложении, которое было таким небрежным? Это напрямую обращалось к объектам-членам и изменяло структуры данных напрямую.

Как вы думаете, почему это стало трудным и даже неприемлемым в современных языках?

39
20.08.2008 22:29:43
+1 за хорошую аналогию между инкапсуляцией и отношениями FK / PK.
jcollum 17.09.2009 20:58:44

Я думаю об этом с точки зрения затрат / выгод ... В MySQL добавление ограничения - это отдельная дополнительная строка DDL . Это всего лишь несколько ключевых слов и пара секунд мысли. Это единственная «стоимость» на мой взгляд ...

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

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

Вопрос:

Есть ли другие варианты использования внешних ключей? Я что-то здесь упускаю?

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

3
10.08.2013 19:35:33

Внешние ключи никогда не были явными (таблица FOREIGN KEY REFERENCES (столбец)), объявленная в проектах (бизнес-приложениях и веб-сайтах социальных сетей), над которыми я работал.

Но всегда существовало своего рода соглашение о присвоении имен столбцам, которые были внешними ключами.

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

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

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

Удаление всех заказов и счетов удаленного клиента с помощью ON DELETE CASCADE - прекрасный пример красивой, но неправильно спроектированной схемы базы данных.

1
10.08.2013 19:37:01

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

8
22.08.2008 15:40:17

Да. ON DELETE [RESTRICT | CASCADE] не позволяет разработчикам прятать данные, обеспечивая чистоту данных. Недавно я присоединился к команде разработчиков Rails, которые не фокусировались на ограничениях базы данных, таких как внешние ключи.

К счастью, я нашел их: http://www.redhillonrails.org/foreign_key_associations.html - Плагины RedHill для Ruby on Rails генерируют внешние ключи, используя соглашение о стиле конфигурации . Миграция с product_id создаст внешний ключ для идентификатора в таблице продуктов .

Посмотрите на другие замечательные плагины в RedHill , в том числе миграции, заключенные в транзакции.

0
10.08.2013 19:38:35

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

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

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

5
17.09.2008 04:44:39

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

Предположим, что программист действительно делает это правильно, тогда действительно ли нам нужна концепция внешних ключей?

Теоретически нет. Тем не менее, никогда не было программного обеспечения без ошибок.

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

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

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

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

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

8
10.08.2013 19:42:25

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

4
17.09.2008 19:09:02

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

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

Предположим, что программист действительно делает это правильно, тогда действительно ли нам нужна концепция внешних ключей?

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

Есть ли другие варианты использования внешних ключей? Я что-то здесь упускаю?

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

5
6.10.2008 21:26:50
Это отличный ответ.
Dan Lugg 23.03.2013 13:49:36

ФК очень важны и должны всегда существовать в вашей схеме, если вы не eBay .

7
19.04.2009 14:53:11
эта ссылка на самом деле чрезвычайно увлекательна ... мне бы очень хотелось узнать больше подробностей, и сейчас я немного боюсь использовать ebay. для других людей: нажмите на 4-й вопрос, чтобы увидеть, что он говорит об их структуре БД. все же интервью стоит посмотреть. также ...unibrow
gloomy.penguin 23.08.2013 18:52:31

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

Например, когда мы делаем предположение: у каждого заказа есть покупатель, и это предположение должно быть чем-то усилено . В базах данных этим «чем-то» являются внешние ключи.

Я думаю, что это стоит компромисса в скорости разработки. Конечно, вы можете быстрее их кодировать, и, вероятно, поэтому некоторые люди их не используют. Лично я убил несколько часов с помощью NHibernate и некоторых ограничений внешнего ключа, которые сердятся, когда я выполняю какую-то операцию. ОДНАКО, я знаю, в чем проблема, так что это меньше проблем. Я использую обычные инструменты, и есть ресурсы, чтобы помочь мне обойти это, возможно, даже люди, чтобы помочь!

Альтернатива - позволить ошибке проникнуть в систему (и при наличии достаточного количества времени), когда внешний ключ не установлен и ваши данные становятся противоречивыми. Затем вы получите необычный отчет об ошибке, расследование и «ОН». База данных прикручена. Теперь, сколько времени это займет, чтобы исправить?

2
10.08.2013 19:45:52

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

0
5.02.2020 17:16:59