Любой пример необходимого внешнего ключа, допускающего значение NULL?

Customers
 customer_id

Orders
 order_id
 customer_id fk

Если у меня есть две таблицы и я определяю внешний ключ для customer_id в таблице «Заказы», ​​разрешая ему быть нулевым, я говорю, что у меня может быть заказ, с которым не связан клиент. Таким образом, понятие внешнего ключа, допускающего значение NULL, кажется несовместимым с целью внешнего ключа, которая заключается в обеспечении соблюдения этого ограничения.

Есть ли простой пример ситуации, в которой необходим внешний ключ, допускающий значение NULL? Или аргумент в пользу их разрешения?


database foreign-key-relationship
person eggdrop    schedule 29.05.2009    source источник

Ответы (8)


arrow_upward
48
arrow_downward

Представьте себе таблицу, содержащую TODO команды. Если TODO еще не назначено члену команды, его user_id равно NULL. Если это не NULL, это внешний ключ к таблице users.

person n3rd    schedule 29.05.2009
comment
Альтернатива, которую многие предпочли бы, состоит в том, чтобы иметь неназначенного пользователя и назначать ему неназначенные TODO. Затем вы можете сделать столбец НЕ NULL. В большинстве книг по проектированию баз данных этот вопрос рассматривается очень подробно. - person ; 29.05.2009
comment
RE: Нил Баттерворт. Я работал с такой системой и могу сказать, что NULL гораздо более очевиден (что может быть лучше для описания ничего, чем ничего). Теперь, если опрос производительности показывает, что внешние ключи NULL являются проблемой, избегайте их, на самом деле это почему система это сделала, но в противном случае вы делаете преждевременную оптимизацию. (ПО МОЕМУ МНЕНИЮ) - person Guvante; 11.07.2009
comment
eggdrop спросил, был ли вариант использования, в котором нулевой FK был необходим, но невозможен. Этот ответ вводит в заблуждение, поскольку предполагает, что такой случай существует в примере, который не поддерживает вывод: добавление таблицы UserTasks (user_id, task_id) с user_id в качестве ее PK и task_id в качестве FK в таблицу TODO будет поддерживать точное тот же вариант использования, только без использования нулей. Вопрос остается открытым, какой подход лучше, но я не верю, что существует вариант использования, при котором невозможно избежать нулевых FK. - person Tomislav Nakic-Alfirevic; 18.05.2011
comment
+1 @Tomislav: Этот ответ не является примером необходимого NULL-FK. Такого не существует. ответ molf является правильным. - person Marcelo Cantos; 18.08.2011
comment
Я никогда не утверждал, что обнуляемые FK необходимы, я просто пытался привести пример, в котором, по моему мнению, это имело бы смысл. Я лично нахожу фиктивные записи временным решением в большинстве случаев, когда значение NULL было бы вполне разумным. Но это, наверное, больше дело вкуса, чем что-либо еще. - person n3rd; 25.08.2011
comment
Можно также иметь таблицу UnassignedTask и таблицу AssignedTask, последняя имеет ограничение FK на User, а первая нет. - person ErikE; 27.12.2012
comment
@TomislavNakic-Alfirevic: Этот вопрос только что возник у меня на радаре, и я только что понял, что в вашей схеме есть некоторые ошибки. user_id не должен быть первичным ключом. Это позволило бы каждому пользователю только одну задачу. task_id должен быть первичным ключом (это означает, что каждая задача может принадлежать не более чем одному пользователю), и оба столбца должны иметь внешние ключи к соответствующим столбцам в таблицах User и Task. - person Marcelo Cantos; 18.06.2013
comment
@n3rd: Что такое фиктивная запись? - person Marcelo Cantos; 18.06.2013
comment
@MarceloCantos Действительно, в предложенном мной изменении схемы есть ошибка. Спасибо, что указали на это. - person Tomislav Nakic-Alfirevic; 18.06.2013
comment
@MarceloCantos: я имел в виду неназначенного пользователя (который не является реальным пользователем), упомянутого аноном в первом комментарии. - person n3rd; 19.06.2013

arrow_upward
16
arrow_downward

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

Вы всегда можете нормализовать необязательную связь 1-many. Взяв ваш пример, у вас могут быть следующие таблицы:

Customers: customer_id, ...
Orders: order_id, ...
OrdersCustomers: order_id, customer_id
  UNIQUE(order_id)

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

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

person molf    schedule 29.05.2009
comment
+1, но я не согласен с последним абзацем. Денормализация только внешне упрощает вещи. Это всегда будет кусать вас в долгосрочной перспективе. Единственная веская причина для этого — приспособиться к существующим механизмам SQL, которые наказывают вас плохой работой при наличии правильной нормализации. (Правильно спроектированный реляционный движок позволит вам определить приведенную выше нормализованную схему, а также автоматизированное и полностью прозрачное сопоставление с эффективной денормализованной структурой хранения, что даст вам лучшее из обоих миров.) - person Marcelo Cantos; 18.08.2011
comment
@Marcelo - Денормализация только внешне упрощает вещи - Неправда. Как показывает пример n3rd, абсолютно точно существуют случаи, когда принудительная нормализация приводит к тому, что становится неясным то, что должно быть необязательным отношением: Что-то, что Nullable FK сразу становится понятным. Если значения NULL запрещены при моделировании необязательных отношений, то должны быть введены настраиваемые значения по умолчанию, что создает сложность и потенциальную путаницу для программистов и администраторов, что, гораздо более вероятно, укусит вас в долгосрочной перспективе. - person Yarin; 16.06.2013
comment
@Yarin: Сценарий, который цитирует n3rd, не требует NULL или специальных значений, и правильно нормализованное решение не скрывает намерения. Наоборот, намерение было бы явным и недвусмысленным: Todo { todoId PK, … }, UserHasTodo { todoId PK FK Todo(todoId), userId FK User(userId) }. - person Marcelo Cantos; 18.06.2013
comment
@Yarin: На самом деле, я только что заметил, что два года назад я проголосовал за комментарий Томислава к ответу n3rd. Он касается именно этого момента и предлагает ту же форму решения, которую только что сделал я (хотя он неправильно понял PK и FK). - person Marcelo Cantos; 18.06.2013
comment
@Marcelo- Я бы сказал, что введение таблицы соединений для этого случая привело бы к сложности (дополнительная таблица) и неясным намерениям (ограничение 0/1-ко-многим больше не применяется БД) - person Yarin; 19.06.2013
comment
@Yarin: добавление таблицы автоматически не квалифицируется как увеличение сложности. Если бы это было правдой, одна таблица, содержащая задачи пользователей и, была бы проще, чем одна таблица для каждого. Кроме того, NULL вызывают всевозможные тонкие ошибки, которые трудно даже диагностировать, не говоря уже об исправлении; они добавляют гораздо больше сложности, чем дополнительная таблица. Что касается комментария о соблюдении ограничений, я не знаю, на какой случай вы ссылаетесь, на тот, что в исходном вопросе, или на пример TODO от n3rd. Но в любом случае ответ molf явно указывает, как обеспечить соблюдение необходимых ограничений. - person Marcelo Cantos; 19.06.2013
comment
@Marcelo- извините, вы правы, я пропустил пример molf- вы действительно можете применить ограничение с помощью нормализованного решения. Все еще не убежден, что это более простое решение, но, тем не менее, хорошее обсуждение. - person Yarin; 19.06.2013
comment
Второй ключ избыточен. (Поскольку каждый order_id уникален, каждая пара order_id-customer_id должна быть уникальной.) Если вы хотите не более одного order_id для каждого customer_id, сделайте customer_id уникальным. - person philipxy; 15.08.2015
comment
@philipxy Я думал о том же. Второе ограничение UNIQUE является избыточным в зависимости от цели. Два уникальных ограничения гарантируют, что один заказ может принадлежать только одному клиенту и никогда одному и тому же клиенту, дважды указанному в ответе. Первое ограничение UNIQUE также гарантирует то же самое. - person Nikunj Madhogaria; 15.08.2015
comment
Это избыточный период, математически. PS Я понимаю, что вы не указали не более одного order_id для каждого customer_id; Я упомянул об этом для полноты/симметрии/сравнения двух связанных типов сущностей. - person philipxy; 16.08.2015

arrow_upward
5
arrow_downward

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

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

person James Curran    schedule 29.05.2009

arrow_upward
3
arrow_downward

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

ALTER TABLE Return_COMMENTS MODIFY order_ID Number NULL;
person mehbooblal mujawar    schedule 06.01.2011
comment
Могу ли я вставить NULL во внешний ключ в таблице? - person Kiquenet; 28.10.2013

arrow_upward
1
arrow_downward

есть, сделать какую-то древовидную структуру, таблицу, которая связана сама с собой. Учти это:

table_node(node_id, parent_node_id, name)

Для корня parent_node_id должен быть нулевым, верно?

person goFrendiAsgard    schedule 09.11.2011
comment
Вы можете возразить, что использование поля is_root было бы лучшим решением, позволяющим избежать необходимости использования внешнего ключа, допускающего значение NULL. Но если принудительно использовать is_root, parent_node_id будет бессмысленным. Должны ли мы быть вынуждены заполнять бессмысленные данные только потому, что они не должны быть нулевыми? - person goFrendiAsgard; 11.11.2011
comment
как насчет того, чтобы просто установить parent_node корневого узла = самому себе. - person Bill Anton; 10.10.2012
comment
хорошая идея, но это усложнит вставку команды, если вы сделаете node_id автоинкрементом. - person goFrendiAsgard; 11.10.2012
comment
да, хороший момент. приложению все равно придется выполнять проверку при извлечении в любом случае... (ГДЕ node_id = parent_node_id) vs (ГДЕ parent_node_id IS NULL)... поэтому я согласен, что использование NULL, вероятно, лучше. - person Bill Anton; 11.10.2012
comment
@goFrendiAsgard Вставить один корневой узел не очень сложно. Вы делаете это только один раз, и вы можете предположить, что это соответствует свойству идентичности, так что ничего страшного. - person ErikE; 27.12.2012
comment
@banton Проверка на NULL всегда кусает вас в долгосрочной перспективе. - person ErikE; 27.12.2012
comment
@EriKE: Хорошо, позвольте мне пересмотреть свой ответ. В некоторых случаях мы используем такую ​​структуру для хранения графообразной структуры (например, меню навигации). Поэтому иногда существует более одного корневого узла. - person goFrendiAsgard; 27.12.2012
comment
@goFrendiAsgard Вам никогда не понадобится более одного корневого узла. Если существует более одного корневого узла, вы создаете настоящий корневой узел над ним. - person ErikE; 27.12.2012
comment
@ErikE: Я имею в виду что-то вроде этого: github.com/goFrendiAsgard/No-CMS/blob/master/install/resources/ В этом случае я не думаю, что лучше добавить 1 строку истинного корневого узла. - person goFrendiAsgard; 28.12.2012
comment
@ErikE: именно так, как показано. Я думаю, когда вы нажмете на ссылку, вы будете перенаправлены на строку 255, не так ли? Есть таблица cms_navigation. Представьте это как меню. Там будет много меню со многими подменю. Каждое меню является корневым. Итак, в этом случае у нас есть более одного корневого узла. И в этом случае сделать одну истинную корневую ноду для меня недостаточно. Как вы думаете? - person goFrendiAsgard; 29.12.2012
comment
@goFrendi Приношу извинения - я перешел по ссылке на своем телефоне и пропустил строку #. Использование NULL - совершенно прекрасный способ сделать это, и я сам разработал таблицы таким образом (я не хотел, чтобы это звучало так, как будто это действительно плохо, просто это не требуется) . Также возможно создание настоящего корневого узла. Также сработает добавление еще одной таблицы отношений «один к нулю или один» для записи родителя. Конечным моментом является то, что внешний ключ NULLable, насколько я могу судить, никогда не требуется. В своем ответе вы говорите, что должен быть NULL, но я с этим не согласен. - person ErikE; 29.12.2012

arrow_upward
0
arrow_downward

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

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

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

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

person Jason Short    schedule 11.07.2009
comment
Если FK находится в родительском объекте, то на самом деле это не отношения родитель-потомок, потому что использование родителя-потомка подразумевает, что может быть много потомков. Когда отношение представляет собой отношение один к нулю или один, оно скорее является а, чем имеет а. В этом случае сделайте дочерний идентификатор таким же, как родительский идентификатор, поместите FK в дочерний элемент (с PK в том же столбце), и проблема с нулевым значением полностью исчезнет. Таким образом, столбец с нулевым значением не требуется, и фактически (на мой взгляд) он уступает использованию шаблона проектирования супертипа/подтипа. - person ErikE; 27.12.2012

arrow_upward
0
arrow_downward

Есть еще одна ситуация, о которой я могу думать:

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

Таблица со следующими столбцами:

  • id как целое число, автоинкремент, не обнуляемый
  • parentid как целое число, допускающее значение NULL.

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

person user35239    schedule 31.07.2013

arrow_upward
0
arrow_downward

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

person HLGEM    schedule 05.01.2011
comment
Это по-прежнему не требуется, так как в столбце FK могут быть специальные неназначенные значения. - person ErikE; 27.12.2012