Что случилось с нулевыми столбцами в составных первичных ключах?
ORACLE не разрешает значения NULL в любом из столбцов, которые содержат первичный ключ. Похоже, что это относится и к большинству других систем уровня предприятия.
В то же время большинство систем также допускают уникальные ограничения для столбцов с нулевым значением.
Почему это единственные ограничения могут иметь NULL, но первичные ключи не могут? Есть ли фундаментальная логическая причина для этого, или это скорее техническое ограничение?
Ответы
Ответ 1
Первичные ключи предназначены для однозначной идентификации строк. Это делается путем сравнения всех частей ключа со входом.
В соответствии с определением NULL не может быть частью успешного сравнения. Даже сравнение с самим собой (NULL = NULL
) потерпит неудачу. Это означает, что ключ, содержащий NULL, не будет работать.
В дополнение, NULL разрешен во внешнем ключе, чтобы отметить необязательное отношение. (*) Разрешить его в PK также нарушить это.
(*) Предупреждение: наличие нулевых внешних ключей не является чистым реляционным дизайном базы данных.
Если есть два объекта A
и B
, где A
необязательно может быть связано с B
, то чистым решением является создание таблицы разрешений (скажем, AB
). Эта таблица свяжет A
с B
: если есть связь, тогда она будет содержать запись, если нет, тогда она не будет.
Ответ 2
Первичный ключ определяет уникальный идентификатор строки each в таблице: когда таблица имеет первичный ключ, у вас есть способ выбрать любую строку из него.
Уникальное ограничение не обязательно определяет каждую строку; он просто указывает, что , если строка имеет значения в своих столбцах, , а затем они должны быть уникальными. Этого недостаточно, чтобы однозначно идентифицировать строку every, что и должен делать первичный ключ.
Ответ 3
В сущности, в NULL в первичном ключе из нескольких столбцов нет ничего плохого. Но иметь такое значение имеет значение, которое, скорее всего, разработчик не имел в виду, поэтому многие системы выдают ошибку, когда вы пытаетесь это сделать.
Рассмотрим случай версий модулей/пакетов, хранящихся в виде последовательности полей:
CREATE TABLE module
(name varchar(20) PRIMARY KEY,
description text DEFAULT '' NOT NULL);
CREATE TABLE version
(module varchar(20) REFERENCES module,
major integer NOT NULL,
minor integer DEFAULT 0 NOT NULL,
patch integer DEFAULT 0 NOT NULL,
release integer DEFAULT 1 NOT NULL,
ext varchar(20),
notes text DEFAULT '' NOT NULL,
PRIMARY KEY (module, major, minor, patch, release, ext));
Первые 5 элементов первичного ключа являются регулярно определяемыми частями продакшен версии, но некоторые пакеты имеют настраиваемое расширение, которое обычно не является целым числом (например, "rc-foo", "vanilla", "beta" или что-то в этом роде). иначе может придумать кто-то, кому недостаточно четырех полей). Если у пакета нет расширения, то в приведенной выше модели он равен NULL, и если оставить все как есть, никакого вреда не будет.
Но что такое NULL? Предполагается, что это нехватка информации, неизвестность. Тем не менее, возможно, это имеет больше смысла:
CREATE TABLE version
(module varchar(20) REFERENCES module,
major integer NOT NULL,
minor integer DEFAULT 0 NOT NULL,
patch integer DEFAULT 0 NOT NULL,
release integer DEFAULT 1 NOT NULL,
ext varchar(20) DEFAULT '' NOT NULL,
notes text DEFAULT '' NOT NULL,
PRIMARY KEY (module, major, minor, patch, release, ext));
В этой версии "ext" часть кортежа НЕ NULL, но по умолчанию это пустая строка, которая семантически (и практически) отличается от NULL. NULL - это неизвестное, тогда как пустая строка - это преднамеренная запись "чего-то, чего нет". Другими словами, "пустой" и "ноль" - это разные вещи. Разница между "у меня нет значения здесь" и "я не знаю, что это за значение здесь".
Когда вы регистрируете пакет, у которого нет расширения версии, вы знаете, что у него нет расширения, поэтому пустая строка на самом деле является правильным значением. Значение NULL будет правильным только в том случае, если вы не знаете, есть ли у него расширение или нет, или вы знали, что оно имеет, но не знали, что это такое. С такой ситуацией легче справиться в системах, где строковые значения являются нормой, потому что нет способа представить "пустое целое число", кроме вставки 0 или 1, которое будет свернуто при любых сравнениях, сделанных позже (которые имеют свои последствия) *.
Кстати, оба способа допустимы в Postgres (так как мы обсуждаем "корпоративные" RDMBS), но результаты сравнения могут немного отличаться, когда вы добавляете NULL в смесь - потому что NULL == "не знаю", так что все результаты сравнения, включающего NULL, заканчиваются тем, что NULL, так как вы не можете знать то, что неизвестно. ОПАСНОСТЬ! Тщательно продумайте это: это означает, что результаты сравнения NULL распространяются через серию сравнений. Это может быть источником незаметных ошибок при сортировке, сравнении и т.д.
Postgres предполагает, что вы взрослый человек, и можете принять это решение самостоятельно. Oracle и DB2 предполагают, что вы не понимаете, что делаете что-то глупое, и выдают ошибку. Обычно это правильно, но не всегда - в некоторых случаях вы можете вообще не знать и иметь значение NULL, и, следовательно, оставить строку с неизвестным элементом, с которым невозможны значимые сравнения, - это правильное поведение.
В любом случае вы должны стремиться исключить количество полей NULL, разрешенных для всей схемы, и вдвойне, когда речь идет о полях, которые являются частью первичного ключа. В подавляющем большинстве случаев наличие столбцов NULL является признаком ненормализованной (в отличие от преднамеренно ненормализованной) схемы схемы, и перед принятием ее следует тщательно обдумать.
[* ПРИМЕЧАНИЕ. Можно создать пользовательский тип, представляющий собой объединение целых чисел и "нижнего" типа, который будет семантически означать "пустой", а не "неизвестный". К сожалению, это вносит некоторую сложность в операции сравнения, и, как правило, истинная правильность типа не стоит усилий на практике, поскольку вам вообще не следует разрешать много значений NULL
. Тем не менее, было бы замечательно, если бы СУБД включали тип BOTTOM
по умолчанию в дополнение к NULL
, чтобы предотвратить привычку случайно смешивать семантику "нет значения" с "неизвестным значением".]
Ответ 4
NULL == NULL → false (по крайней мере, в СУБД)
Таким образом, вы не сможете получить какие-либо отношения, используя значение NULL, даже с дополнительными столбцами с реальными значениями.
Ответ 5
Ответ Тони Эндрюса - достойный. Но реальный ответ заключается в том, что это было соглашение, используемое сообществом реляционных баз данных, и НЕ является необходимостью. Может быть, это хорошая конвенция, может быть, нет.
Сравнение чего-либо с результатами NULL в UNKNOWN (третье значение истины).
Так, как было предложено с нулями, вся традиционная мудрость относительно равенства выходит из окна. Ну, как это кажется на первый взгляд.
Но я не думаю, что это обязательно так, и даже базы данных SQL не думают, что NULL уничтожает все возможности для сравнения.
Запустите в своей базе данных запрос
SELECT * FROM VALUES (NULL)
UNION
SELECT * FROM VALUES (NULL)
То, что вы видите, это всего лишь один кортеж с одним атрибутом, который имеет значение NULL.
Таким образом, объединение признало здесь два значения NULL равными.
При сравнении составного ключа, который имеет 3 компонента для кортежа с 3 атрибутами (1, 3, NULL) = (1, 3, NULL) <= > 1 = 1 AND 3 = 3 AND NULL = NULL
Результатом этого является UNKNOWN.
Но мы могли бы определить новый тип оператора сравнения, например. ==.
X == Y <= > X = Y OR (X NULL И Y NULL)
Наличие такого оператора равенства создаст составные ключи с нулевыми компонентами или некомпозитный ключ с нулевым значением без проблем.
Ответ 6
Я по-прежнему считаю, что это фундаментальный/функциональный недостаток, вызванный техникой. Если у вас есть необязательное поле, по которому вы можете идентифицировать клиента, вам теперь придется взломать в него фиктивное значение, просто потому, что NULL!= NULL, не особенно элегантный, но это "отраслевой стандарт"
Ответ 7
Для меня, если я хочу PK с нулевым значением, я должен быть в состоянии сделать это...
Для меня это говорит: "Я знаю, что, возможно, не знаю, что это за столбец, но у меня может быть только один экземпляр, в котором я не знаю, что это за столбец для каждой возможности оставшихся элементов в Ключе".