NULL разрешено в основном ключе - почему и в какой СУБД?

В дополнение к моему вопросу "Зачем использовать" нулевой первичный ключ "в TSQL?" ...

Как я понял из других обсуждений, некоторые РСУБД (например, SQLite, ~~MySQL~~) допускают "уникальный" NULL в первичном ключе.

Почему это разрешено и как оно может быть полезно?

Предпосылки: Я считаю, что полезно общаться с коллегами и профессионалами базы данных, чтобы знать различия в фундаментальных концепциях, подходах и их реализации в разных СУБД.

Примечания

MySQL восстанавливается и возвращается в список "NOT NULL PK".
SQLite добавлен (благодаря Полю Хэдфилду) в список "NULL PK":

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

Если оператор INSERT или UPDATE пытается изменить содержимое таблицы, так что две или несколько строк имеют одинаковые значения первичного ключа, это нарушение ограничения. Согласно стандарту SQL, PRIMARY KEY всегда должен подразумевать NOT NULL. К сожалению, из-за давнего контроля над кодированием это не относится к SQLite.

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

Итак, теперь мы решили продолжить использование NULL в столбцах PRIMARY KEY. Однако разработчики должны знать, что мы можем изменить SQLite для соответствия стандарту SQL в будущем и соответствующим образом разработать новые программы.

- SQL как понимается SQLite: CREATE TABLE

Ответ 1

Предположим, что у вас есть первичный ключ, содержащий нулевой столбец Kn.

Если вы хотите, чтобы вторая строка была отвергнута на том основании, что в этой второй строке Kn является нулевым, а таблица уже содержит строку с Kn null, тогда вы фактически требуете, чтобы система рассматривала сравнение "row1. Kn = row2.Kn" как указание TRUE (потому что вы как-то хотите, чтобы система обнаружила, что значения ключей в этих строках действительно равны). Однако это сравнение сводится к сравнению "null = null", а стандарт уже явно указывает, что значение null не сравнивается ни с чем, включая себя.

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

Ответ 2

Я не знаю, отличаются ли предыдущие версии MySQL, но в современных версиях первичный ключ должен быть в столбцах, которые не равны нулю. См. справочную страницу на CREATE TABLE:" A PRIMARY KEY - уникальный индекс, где все ключевые столбцы должны быть определены как NOT NULL. они явно не объявлены как NOT NULL, MySQL объявляет их так неявно (и молча).

Ответ 3

Что касается теории реляционных баз данных:

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

В зависимости от данных, которые вы моделируете, вместо NULL можно использовать значение "сделанное". Я использовал 0, "N/A", "1 января 1980 года" и аналогичные значения, чтобы представлять фиктивные "неизвестные" данные.

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

Ответ 4

Ну, это может позволить вам реализовать Null Object Pattern изначально в базе данных. Поэтому, если вы использовали что-то похожее в коде, которое очень близко взаимодействовало с БД, вы могли просто искать объект, соответствующий ключу, не имея специального случая с нулевой проверкой.

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

Ответ 5

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

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

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

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

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

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

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

Ответ 6

Наличие первичного нулевого ключа может быть полезным в некоторых сценариях. В одном из моих проектов я использовал эту функцию во время синхронизации баз данных: один на сервере и многие на разных пользовательских устройствах. Учитывая тот факт, что не все пользователи имеют доступ к Интернету все время, я решил, что только основная база данных сможет предоставить идентификаторы моим сущностям. SQLite имеет собственный механизм для нумерации строк. Если бы я использовал дополнительное поле id, я бы использовал больше полосы пропускания. Наличие null как id не только уведомляет меня о том, что объект создан на клиентском устройстве, когда он не имел доступа к Интернету, но также уменьшает сложность кода. Единственный недостаток заключается в том, что на клиентском устройстве я не могу получить объект по его идентификатору, если он ранее не был синхронизирован с основной базой данных. Однако это не проблема, так как мой пользователь заботится о сущности для своих параметров, а не их уникального идентификатора.