Что такое null значение в базах данных
SQL Значение NULL
Что такое значение NULL?
Если поле в таблице является необязательным, то можно вставить новую запись или обновить запись без добавления значения в это поле. Затем поле будет сохранено с значением NULL.
Как проверить наличие NULL значений?
Вместо этого нам придется использовать оператор IS NULL и IS NOT NULL.
Синтаксис IS NULL
Синтаксис IS NOT NULL
Демо база данных
Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Оператор IS NULL
Оператор IS NULL используется для проверки пустых значений (NULL).
В следующем SQL файле перечислены все клиенты с значением NULL в поле «Address»:
Пример
Совет: Всегда используйте значение NULL для поиска значений NULL.
Оператор IS NOT NULL
Оператор IS NOT NULL используется для проверки непустых значений (NOT NULL).
В следующем SQL файле перечислены все клиенты со значением в поле «Address»:
NULL (SQL)
NULL в Системах управления базами данных (СУБД) — специальное значение (псевдозначение), которое может быть записано в поле таблицы базы данных (БД). NULL соответствует понятию «пустое поле», то есть «поле, не содержащее никакого значения». Введено для того, чтобы различать в полях БД пустые (визуально не отображаемые) значения (например, строку нулевой длины) и отсутствующие значения (когда в поле не записано вообще никакого значения, даже пустого).
NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни нулю. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL!
Содержание
Необходимость NULL в реляционных БД
Использование NULL в БД
В БД, поддерживающих понятие NULL, для поля таблицы при описании определяется, может ли оно быть пустым. Если да, то в это поле можно не записывать никакого значения, и это поле будет иметь значение NULL. Также можно и явно записать в такое поле значение NULL.
Как правило, СУБД не разрешает значение NULL для полей, являющихся частью первичного ключа таблицы. В полях внешних ключей, напротив, NULL допускается. Наличие NULL в поле внешнего ключа может трактоваться как признак отсутствия связанной записи, и для такого внешнего ключа не требуется исполнение правил ссылочной целостности, обязательных для любого другого значения внешнего ключа.
Операции с NULL
Поскольку NULL не является, в общем смысле, значением, использование его в арифметических, строковых, логических и других операциях, строго говоря, некорректно. Тем не менее, большинство СУБД поддерживают такие операции, но вводят для них специальные правила:
Кроме того, могут существовать специальные системные функции для удобного преобразования NULL к определённым значениям, например, в Oracle имеется системная функция NVL, которая возвращает значение своего параметра, если он не NULL, или значение по умолчанию, если операнд — NULL. В стандарте SQL-92 определены две функции: NULLIF и COALESCE, поэтому их использование является более предпочтительным (если конкретная СУБД их реализует).
NULL в SQL
NULL — спец. знач. (псевдозначение) в ЯЗ SQL и большинстве СУБД, в частности, что может быть записано в поле таблицы БД.
Содержание
Синопсис
NULL не явл. каким-либо типом данных и соответствует понятию «пустое поле», т.е. «поле, не содержащее никакого значения». Введено для того, чтобы различать в полях БД пустые (визуально не отображаемые) значения (напр., строку нулевой длины) и отсутствующие значения, когда в поле не записано вообще никакого значения, даже пустого.
Использование NULL в БД
Как правило, СУБД не разрешает значение NULL для полей, являющихся частью первичного ключа таблицы. В полях внешних ключей, напротив, NULL допускается. Наличие NULL в поле внешнего ключа может трактоваться как признак отсутствия связанной записи, и для такого внешнего ключа не требуется исполнение правил ссылочной целостности, обязательных для любого другого значения внешнего ключа.
Проверка значения на NULL
Для проверки значения поля таблицы на пустоту (т.е. NULL ) используется выражение типа is NULL и is not NULL соответственно:
Операции с NULL
Поскольку NULL не является, в общем смысле, значением, использование его в арифметических, строковых, логических и других операциях, строго говоря, некорректно. Т.н.м., большинство СУБД поддерживают такие операции, но вводят для них специальные правила:
Подробнее о NULL
Предположим, что вы получили новую запись, которая ещё не была назначена записи из др. табл. Чем ждать внесения др. записи, к которой текущую нужно назначить, вы можете ввести запись в БД сразу же, так, что она не потеряется при перестановке. Вы можете ввести строку со знач. NULL в соотв. поле, и заполнить это поле позже, когда соотв. запись в др. табл. будет внесена.
Сравнение с NULL
Примеры
Найдем все записи в табл. Customers с знач. NULL в столбце city:
Здесь не будет никакого вывода, потому что мы не имеем никаких значений NULL в табл.
Что такое null значение в базах данных
Прежде, чем говорить о целостности сущностей, опишем использование null-значений в реляционных базах данных.
Null-значения
Таким образом, в ситуации, когда возможно появление неизвестных или неполных данных, разработчик имеет на выбор два варианта.
Второй вариант состоит в использовании null-значений вместо неизвестных данных. За кажущейся естественностью такого подхода скрываются менее очевидные и более глубокие проблемы. Наиболее бросающейся в глаза проблемой является необходимость использования трехзначной логики при оперировании с данными, которые могут содержать null-значения. В этом случае при неаккуратном формулировании запросов, даже самые естественные запросы могут давать неправильные ответы. Есть более фундаментальные проблемы, связанные с теоретическим обоснованием корректности введения null-значений, например, непонятно вообще, входят ли null-значения в домены или нет.
Подробное обсуждение проблем использования null-значений выходит за пределы данной работы. Можно только сказать о том, что этот вопрос в теории реляционных баз данных окончательно не решен. Основоположник реляционного подхода Кодд считал null-значения неотъемлемой частью реляционной модели. К.Дейт, один из крупнейших теоретиков реляционной модели выступает категорически против null-значений (подробное обсуждение проблем, возникающих при использовании null-значений приведено в книге [11].
Практически все реализации современных реляционных СУБД позволяют использовать null-значения, несмотря на их недостаточную теоретическую обоснованность. Такую ситуацию можно сравнить с ситуацией, сложившейся в начале века с теорией множеств. Почти сразу после создания Кантором теории множеств, в ней были обнаружены внутренние противоречия (антиномии). Были разработаны более строгие теории, позволяющие избежать этих противоречий (конструктивная теория множеств). Однако в реальной работе большинство математиков пользуется классической теорией множеств, т.к. более строгие теории более ограничены и негибки в применении именно в силу своей большей строгости.
Мнение автора (очень скромное по сравнению с мнением корифеев реляционной теории) состоит в том, что желательно избегать null-значений. Тем не менее, приведем здесь описание трехзначной логики, необходимой для работы с null-значениями.
Трехзначная логика (3VL)
Т.к. null-значение обозначает на самом деле тот факт, что значение неизвестно, то любые алгебраические операции (сложение, умножение, конкатенация строк и т.д.) должны давать также неизвестное значение, т.е. null. Действительно, если, например, вес детали неизвестен, то неизвестно также, сколько весят 10 таких деталей.
| AND | F | T | U |
|---|---|---|---|
| F | F | F | F |
| T | F | T | U |
| U | F | U | U |
Таблица 1 Таблица истинности AND
| OR | F | T | U |
|---|---|---|---|
| F | F | T | U |
| T | T | T | T |
| U | U | T | U |
Таблица 2 Таблица истинности OR
| NOT | |
|---|---|
| F | T |
| T | F |
| U | U |
Таблица 3 Таблица истинности NOT
Имеется несколько парадоксальных следствий применения трехзначной логики.
Парадокс 1. Null-значение не равно самому себе. Действительно, выражение null = null дает значение не ИСТИНА, а НЕИЗВЕСТНО. Значит выражение 
не обязательно ИСТИНА!
Парадокс 2. Неверно также, что null-значение не равно самому себе! Действительно, выражение null
null также принимает значение не ИСТИНА, а НЕИЗВЕСТНО! Значит также, что и выражение 
тоже не обязательно ЛОЖЬ!
Парадокс 3. 
не обязательно ИСТИНА. Значит, в трехзначной логике не работает принцип исключенного третьего (любое высказывание либо истинно, либо ложно).
Таких парадоксов можно построить сколько угодно. Конечно, это на самом деле не парадоксы, а просто следствия из аксиом трехзначной логики.
Потенциальные ключи
По определению, тело отношения есть множество кортежей, поэтому отношения не могут содержать одинаковые кортежи. Это значит, что каждый кортеж должен обладать свойством уникальности. На самом деле, свойством уникальности в пределах отношения могут обладать отдельные атрибуты кортежей или группы атрибутов. Такие уникальные атрибуты удобно использовать для идентификации кортежей.
Любое отношение имеет по крайней мере один потенциальный ключ. Действительно, если никакой атрибут или группа атрибутов не являются потенциальным ключом, то, в силу уникальности кортежей, все атрибуты вместе образуют потенциальный ключ.
Потенциальный ключ, состоящий из одного атрибута, называется простым. Потенциальный ключ, состоящий из нескольких атрибутов, называется составным.
Замечание. Понятие потенциального ключа является семантическим понятием и отражает некоторый смысл (трактовку) понятий из конкретной предметной области. Для того чтобы проиллюстрировать этот факт рассмотрим следующее отношение «Сотрудники»:
| Табельный номер | Фамилия | Зарплата |
|---|---|---|
| 1 | Иванов | 1000 |
| 2 | Петров | 2000 |
| 3 | Сидоров | 3000 |
Таблица 4 Отношение «Сотрудники»
Попробуем представить это отношение в другом виде, изменив наименования атрибутов:
| A | B | C |
|---|---|---|
| 1 | Иванов | 1000 |
| 2 | Петров | 2000 |
| 3 | Сидоров | 3000 |
Замечание. Потенциальные ключи служат средством идентификации объектов предметной области, данные о которых хранятся в отношении. Объекты предметной области должны быть различимы.
Замечание. Потенциальные ключи служат единственным средством адресации на уровне кортежей в отношении. Точно указать какой-нибудь кортеж можно только зная значение его потенциального ключа.
Целостность сущностей
Т.к. потенциальные ключи фактически служат идентификаторами объектов предметной области (т.е. предназначены для различения объектов), то значения этих идентификаторов не могут содержать неизвестные значения. Действительно, если бы идентификаторы могли содержать null-значения, то мы не могли бы дать ответ «да» или «нет» на вопрос, совпадают или нет два идентификатора.
Это определяет следующее правило целостности сущностей:
Правило целостности сущностей. Атрибуты, входящие в состав некоторого потенциального ключа не могут принимать null-значений.
Внешние ключи
Различные объекты предметной области, информация о которых хранится в базе данных, всегда взаимосвязаны друг с другом. Например, накладная на поставку товара содержит список товаров с количествами и ценами, сотрудник предприятия имеет детей, числится в подразделении и т.д. Термины «содержит», «имеет», «числится» отражают взаимосвязи между понятиями «накладная» и «список товаров», «сотрудник» и «дети», «сотрудник» и «подразделение». Такие взаимосвязи отражаются в реляционных базах данных при помощи внешних ключей, связывающих несколько отношений.
Рассмотрим пример с поставщиками и поставками деталей. Предположим, что нам требуется хранить информацию о наименовании поставщиков, наименовании и количестве поставляемых ими деталей, причем каждый поставщик может поставлять несколько деталей и каждая деталь может поставляться несколькими поставщиками. Можно предложить хранить данные в следующем отношении:
| Номер поставщика | Наименование поставщика | Номер детали | Наименование детали | Поставляемое количество |
|---|---|---|---|---|
| 1 | Иванов | 1 | Болт | 100 |
| 1 | Иванов | 2 | Гайка | 200 |
| 1 | Иванов | 3 | Винт | 300 |
| 2 | Петров | 1 | Болт | 150 |
| 2 | Петров | 2 | Гайка | 250 |
| 3 | Сидоров | 3 | Винт | 1000 |
Таблица 5 Отношение «Поставщики и поставляемые детали»
Потенциальным ключом этого отношения может выступать пара атрибутов <"Номер поставщика", "Номер детали">— в таблице они выделены курсивом.
Приведенный способ хранения данных обладает рядом недостатков.
Что произойдет, если изменилось наименование поставщика? Т.к. наименование поставщика повторяется во многих кортежах отношения, то это наименование нужно одновременно изменить во всех кортежах, где оно встречается, иначе данные станут противоречивыми. То же самое с наименованиями деталей. Значит, данные хранятся в нашем отношении с большой избыточностью.
Далее, как отразить факт, что некоторый поставщик, например Петров, временно прекратил поставки деталей? Если мы удалим все кортежи, в которых хранится информация о поставках этого поставщика, то мы потеряем данные о самом Петрове как потенциальном поставщике. Выйти из этого положения, оставив в отношении кортеж типа (2, Петров, NULL, NULL, NULL) мы не можем, т.к. атрибут «Номер детали» входит в состав потенциального ключа и не может содержать null-значений. То же самое произойдет, если некоторая деталь временно не поставляется никаким поставщиком. Получается, что мы не можем хранить информацию о том, что есть некий поставщик, если он не поставляет хотя бы одну деталь, и не можем хранить информацию о том, что есть некоторая деталь, если она никем не поставляется.
Эти фразы отражают различные типы взаимосвязей. Чтобы более точно отразить предметную область, можно иначе переформулировать фразы: «Один Поставщик может выполнять несколько Поставок», «Одна Деталь может поставляться несколькими Поставками». Это пример взаимосвязи типа «один-ко-многим«.
Взаимосвязь между «Поставщиками» и «Деталями» можно переформулировать так: «Несколько Деталей может поставляться несколькими Поставщиками». Это пример взаимосвязи типа «много-ко-многим«.
В реляционных базах данных основными являются взаимосвязи типа «один-ко-многим». Взаимосвязи типа «много-ко-многим» реализуются использованием нескольких взаимосвязей типа «один-ко-многим». Отношение, входящее в связь со стороны «один» (например, «Поставщики»), называют родительским отношением. Отношение, входящее в связь со стороны «много» (например, «Поставки»), называется дочернем отношением.
Механизм реализации взаимосвязи «один-ко-многим» состоит в том, что в дочернее отношение добавляются атрибуты, являющиеся ссылками на ключевые атрибуты родительского отношения. Эти атрибуты и являются внешними ключами, определяющими, с какими кортежами родительского отношения связаны кортежи дочернего отношения. Такие атрибуты еще называют мигрирующими из родительского отношения.
Таким образом, наш пример с поставщиками и поставляемыми деталями должен выглядеть следующим образом:
| Номер поставщика | Наименование поставщика |
|---|---|
| 1 | Иванов |
| 2 | Петров |
| 3 | Сидоров |
Таблица 6 Отношение «Поставщики»
| Номер детали | Наименование детали |
|---|---|
| 1 | Болт |
| 2 | Гайка |
| 3 | Винт |
Таблица 7 Отношение «Детали»
| Номер поставщика | Номер детали | Поставляемое количество |
|---|---|---|
| 1 | 1 | 100 |
| 1 | 2 | 200 |
| 1 | 3 | 300 |
| 2 | 1 | 150 |
| 2 | 2 | 250 |
| 3 | 3 | 1000 |
Таблица 8 Отношение «Поставки»
В отношении «Поставки» атрибуты «Номер поставщика» и «Номер детали» являются ссылками на ключевые атрибуты отношений «Поставщики» и «Детали», и, следовательно, являются внешними ключами. Заметим, что данные отношения свободны от недостатков, описанных выше, когда все данные предлагалось хранить в одном отношении. Действительно, при изменении наименования поставщика или детали, это изменение происходит только в одном месте. Если поставщик прекратил поставки всех деталей, то удаляются соответствующие кортежи в отношении «Поставки», данные же о самом поставщике остаются без изменений.
Дадим точное определение.
Отношение 
называется родительским отношением, отношение 
называется дочерним отношением.
Замечание. Внешний ключ, также как и потенциальный, может быть простым и составным.
Замечание. Внешний ключ должен быть определен на тех же доменах, что и соответствующий первичный ключ родительского отношения.
Замечание. Внешний ключ, как правило, не обладает свойством уникальности. Так и должно быть, т.к. в дочернем отношении может быть несколько кортежей, ссылающихся на один и тот же кортеж родительского отношения. Это, собственно, и дает тип отношения «один-ко-многим».
Замечание. Если внешний ключ все-таки обладает свойством уникальности, то связь между отношениями имеет тип «один-к-одному«. Чаще всего такие отношения объединяются в одно отношение, хотя это и не обязательно.
Замечание. Хотя каждое значение внешнего ключа обязано совпадать со значениями потенциального ключа в некотором кортеже родительского отношения, то обратное, вообще говоря, неверно. Например, могут существовать поставщики, не поставляющие никаких деталей.
Замечание. Для внешнего ключа не требуется, чтобы он был компонентом некоторого потенциального ключа (как получилось в примере с поставщиками и деталями).
Замечание. Null-значения для атрибутов внешнего ключа допустимы только в том случае, когда атрибуты внешнего ключа не входят в состав никакого потенциального ключа
Целостность внешних ключей
Т.к. внешние ключи фактически служат ссылками на кортежи в другом (или в том же самом) отношении, то эти ссылки не должны указывать на несуществующие объекты. Это определяет следующее правило целостности внешних ключей:
Правило целостности внешних ключей. Внешние ключи не должны быть несогласованными, т.е. для каждого значения внешнего ключа должно существовать соответствующее значение первичного ключа в родительском отношении.
Замечания к правилам целостности сущностей и внешних ключей
На самом деле приведенные правила целостности сущностей и внешних ключей прямо следуют из определений понятий «потенциальный ключ» и «внешний ключ».
Для внешних ключей правило целостности фактически входит в определение (п. 2 определения 2).
Тем не менее, явная формулировка правил целостности имеет определенный практический смысл. В большинстве серьезных СУБД за выполнением этих ограничений следит сама СУБД, если, конечно, пользователь явно объявил потенциальные и внешние ключи. Но, во-первых, для некоторых систем можно допустить, чтобы эти ограничения не выполнялись, а во-вторых, некоторые системы просто не поддерживают понятия целостности, например, некоторые «настольные» СУБД типа FoxPro 2.5. В этих случаях за целостностью данных должен следить сам пользователь, или программист, разрабатывающий приложение для пользователя.
Явная формулировка правил целостности помогает четко понять, какие опасности несет в себе пренебрежение этими правилами.
Операции, могущие нарушить ссылочную целостность
Для родительского отношения
Для дочернего отношения
Стратегии поддержания ссылочной целостности
Эти стратегии являются стандартными и присутствуют во всех СУБД, в которых имеется поддержка ссылочной целостности.
Конечно, это не стратегия, а отказ от поддержки ссылочной целостности. В этом случае в дочернем отношении могут появляться некорректные значения внешних ключей, и вся ответственность за целостность базы данных ложится на пользователя.
В дополнение к приведенным стратегиям пользователь может придумать свою уникальную стратегию поддержания ссылочной целостности.
Применение стратегий поддержания ссылочной целостности
Рассмотрим, как применяются стратегии поддержания ссылочной целостности при выполнении операций модификации базы данных.
При обновлении кортежа в родительском отношении
При удалении кортежа в родительском отношении
При вставке кортежа в дочернее отношение
При обновлении кортежа в дочернем отношении
Выводы
Современные СУБД допускают использование null-значений, т.к. данные часто бывают неполными или неизвестными. Споры о допустимости использования null-значений ведутся до сих пор. Использование null-значения связано с применением трехзначной логики (three-valued logic, 3VL).
Средством, позволяющим однозначно идентифицировать кортежи отношения, являются потенциальные ключи отношения.
Потенциальный ключ, состоящий из одного атрибута, называется простым. Потенциальный ключ, состоящий из нескольких атрибутов, называется составным.
Отношения связываются друг с другом при помощи внешних ключей.
Внешний ключ не обязан обладать свойством уникальности. Поэтому, одному кортежу родительского отношения может соответствовать несколько кортежей дочернего отношения. Такой тип связи между отношениями называется «один-ко-многим«.
Связи типа «много-ко-многим» реализуются использованием нескольких отношений типа «один-ко-многим».
Правило целостности сущностей состоит в том, что атрибуты, входящие в состав некоторого потенциального ключа не могут принимать null-значений.
Правило целостности внешних ключей состоит в том, что внешние ключи не должны ссылаться на отсутствующие в родительском отношении кортежи, т.е. внешние ключи должны быть корректны.
Ссылочную целостность могут нарушить операции, изменяющие состояние базы данных. Такими операциями являются операции вставки, обновления и удаления кортежей.
Пользователь может разработать свою уникальную стратегию поддержания ссылочной целостности.