Что означает/должно иметь значение NULL вместе с отношениями FK - База данных

Ответ 1

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

Подумайте о таблице транспортных средств и двигателей, а двигатели еще не установлены на транспортном средстве (поэтому VehicleID имеет значение null). Или таблицу Employee с колонкой Supervisor и генеральным директором компании.

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

CREATE TABLE [dbo].[EngineTable](
    [EngineID] [int] IDENTITY(1,1) NOT NULL,
    [EngineCylinders] smallint NOT NULL,
 CONSTRAINT [EngineTbl_PK] PRIMARY KEY NONCLUSTERED 
(
    [EngineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CarTable](
    [CarID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [EngineID] [int] NULL
 CONSTRAINT [PK_UnitList] PRIMARY KEY CLUSTERED 
(
    [CarID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[CarTable]  WITH CHECK ADD CONSTRAINT [FK_Engine_Car] FOREIGN KEY([EngineID])
REFERENCES [dbo].[EngineTable] ([EngineID])


Insert Into EngineTable (EngineCylinders) Values (4);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (8);

- Теперь несколько тестов:

Insert Into CarTable (Model, EngineID) Values ('G35x', 3);  -- References the third engine

Insert Into CarTable (Model, EngineID) Values ('Sienna', 13);  -- Invalid FK reference - throws an error

Insert Into CarTable (Model) Values ('M');  -- Leaves null in the engine id field & does NOT throw an error 

Ответ 2

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

Варианты использования для отношения "еще не установлено" действительны, но с нулевыми FK некоторые считают, что он добавляет сложности к их запросам, введя более сложные функции SQL, в частности LEFT JOINs.

Одним из распространенных альтернативных решений, которые я видел, является введение "нулевой строки" или "строки дозорного" в каждую таблицу с помощью pk = 0 или pk = 1 (на основе того, что поддерживается вашей РСУБД). Это позволяет вам создать слой домена с "еще не установленными" отношениями, но также избегать введения LEFT JOINs, поскольку вы гарантируете, что с ними всегда будет что-то, с чем можно присоединиться.

Конечно, этот подход требует усердия, потому что вы в основном торгуете ЛЕВЫМИ ПРИСОЕДИНЯМИ, чтобы проверить присутствие вашей строки-дозорника в запросах, чтобы вы не обновляли/не удаляли ее и т.д. Независимо от того, оправданы, это другое дело. Я склонен согласиться с тем, что повторное использование null только для того, чтобы избежать подключения fancier, кажется немного глупым, но я также работал в среде, где разработчики приложений не выигрывают дебаты против администраторов баз данных.

редактирует

Я удалил некоторые из "сути вопроса" и попытался уточнить, что я имел в виду под "неудачными" объединениями. Пример @wcoenen - причина, по которой я лично чаще всего слышал об исключении нулевых FK. Это не то, что они терпят неудачу, как в "сломанной", а скорее неспособны, - утверждают некоторые, - придерживаться принципа наименьшего удивления.

Кроме того, я превратил этот ответ в wiki, так как я по существу убил его из исходного состояния и заимствовал из других сообщений.

Ответ 3

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

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

Ответ 4

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

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

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

Ответ 5

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

Ответ 6

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

SELECT * FROM customer, country WHERE customer.countryID = country.ID

Любой клиент, где страна FK null будет молча пропущена из отчета (вместо этого вы должны использовать LEFT JOIN, чтобы исправить его). Я нахожу это неинтуитивным и удивительным, поэтому мне не нравятся NULL FK и избегаю их в схемах моей базы данных. Вместо этого я использую дозорные значения, например. особая "страна без названия".

Ответ 7

CREATE TABLE [tree]
{
    [id] int NOT NULL,
    [parent_id] int NULL
};

ALTER TABLE [tree] ADD CONSTRAINT [FK_tree_tree] FOREIGN KEY([parent_id])
REFERENCES [tree] ([id]);

Нет ничего плохого в этом! Корень node будет вечно иметь родителя NULL, и это не относится к отношениям "еще не установлено". Нет проблем с присоединениями здесь.

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

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

Я понимаю, что если вы являетесь администратором баз данных, работающим со сверхбольшими базами данных, имеющими сотни миллионов строк, вам не нужны внешние ключи NULL, потому что они просто не будут выполняться. Правда в том, что большинство разработчиков никогда не будут работать с такими большими базами данных в своей жизни, и сегодня базы данных могут справиться с такой ситуацией просто с несколькими сотнями тысяч строк. Чтобы подчеркнуть (бедную) метафору, большинство из нас так не управляют гоночными автомобилями Формулы-1, а автоматическая коробка передач у моей жены Аккорд делает то, что она должна делать просто отлично (или, по крайней мере, раньше, пока она не сломалась несколько недель назад...).

Ответ 8

Если вы назначаете NULL в Business Reason, вы, по сути, переопределяете, что означает NULL в вашем домене, и должны документировать это для пользователей и будущих разработчиков. Если у вас есть бизнес-обоснование наличия NULL в качестве внешнего ключа, я бы предложил вам сделать то же, что и другие, и добавить запись присоединения, которая имеет значение чего-то вроде строк "N/A" или "Не назначено".

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

Ответ 9

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

Ответ 10

У тебя все получилось. Для FK значение NULL означает отсутствие значения (что означает отсутствие отношения). Если в FK есть значение, оно должно соответствовать точно одному значению в PK, которое оно ссылается.

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

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

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

Я сам не следую этой практике, но она имеет определенные преимущества.

Ответ 11

Я бы сказал, что, несмотря на то, что это явно возможно, в чем проблема с использованием таблицы соединения в соответствии с точкой зрения Джонатона Леффлера?

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

Затем, чтобы упростить мои запросы, я просто сделал LEFT JOINing две таблицы вместе, что дает появление необязательного соединения, но на самом деле использует более ясную структуру базы данных. Кроме того, используя ISNULL (MyField, "None" ), на мой взгляд, я могу предоставить преимущества "нет" дополнительного дизайна строк, но без боли.

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

Ответ 12

Таблица соединений - правильный метод.

Углы в ключах указывают на плохой дизайн базы данных.

Значение null не не назначено/пустое/пустое/etc, оно отсутствует/неизвестные данные.

Использование нулей в поле внешнего ключа не означает "нет никакого отношения", это означает "Я не знаю, есть ли отношение или нет" - что явно плохо.