Allow nulls sql что это

Столбцы, содержащие значение NULL по умолчанию

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

Фраза ELEMENTS XSINIL показана в следующем примере Transact-SQL SELECT.

SELECT EmployeeID as "@EmpID", FirstName as "EmpName/First", MiddleName as "EmpName/Middle", LastName as "EmpName/Last" FROM HumanResources.Employee E, Person.Contact C WHERE E.EmployeeID = C.ContactID AND E.EmployeeID=1 FOR XML PATH, ELEMENTS XSINIL; 

Ниже показан результат. Если XSINIL не указан, элемент будет отсутствует.

  Gustavo Achong   

См. также

Обработка значений NULL

Значение NULL в реляционной базе данных используется, если значение в столбце неизвестно или отсутствует. NULL не является ни пустой строкой (для типов данных character или datetime), ни нулевым значением (для числовых типов данных). В спецификации ANSI SQL-92 указано, что значение NULL должно быть одинаковым для всех типов данных, чтобы все значения NULL обрабатывались согласованно. Пространство имен System.Data.SqlTypes обеспечивает семантику со значением NULL, реализуя интерфейс INullable. Каждый из типов данных в System.Data.SqlTypes имеет собственное свойство IsNull и значение Null , которое может быть назначено экземпляру этого типа данных.

В платформа .NET Framework версии 2.0 появилась поддержка типов значений, допускающих значение NULL, которые позволяют программистам расширять тип значения для представления всех значений базового типа. Эти типы значений CLR, допускающих значение NULL, представляют экземпляр Nullable структуры . Эта возможность особенно полезна, если типы значений упакованы и распакованы, что обеспечивает улучшенную совместимость с типами объектов. Типы значений, допускающих значение NULL в среде CLR, не предназначены для хранения значений NULL базы данных, так как ANSI SQL не ведет себя так же, как null ссылка (или Nothing в Visual Basic). Для работы со значениями NULL в базе данных ANSI SQL используйте значения NULL System.Data.SqlTypes вместо Nullable. Дополнительные сведения о работе с типами значений CLR, допускающих значение NULL, в Visual Basic см. в разделе Типы значений, допускающих значение NULL, а для C# — в разделе Типы значений, допускающих значение NULL.

Значения NULL и тройственная логика

Разрешение значений NULL в определениях столбцов вводит в приложение логику трех значений. Результатом сравнения может быть одно из трех условий:

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

Значения NULL и SqlBoolean

При сравнении между любыми типами System.Data.SqlTypes будет возвращаться значение SqlBoolean. Функция IsNull для каждого типа SqlType возвращает SqlBoolean и может использоваться для проверки на наличие значений NULL. В следующих таблицах истинности показано, как работают операторы AND, OR и NOT при наличии значения NULL. (T = true, F = false и U = неизвестно или NULL.)

Основные сведения о параметре ANSI_NULLS

System.Data.SqlTypes предоставляет ту же семантику, что и при установке параметра ANSI_NULLS в SQL Server. Все арифметические операторы (+, -, *, /, %), битовые операции (~, &, |) и большинство функций возвращают NULL, если какие-либо из операндов или аргументов равны NULL, за исключением операндов или аргументов для свойства IsNull .

Стандарт ANSI SQL-92 не поддерживает columnName = NULL в предложении WHERE. В SQL Server параметр ANSI_NULLS управляет допустимостью значений NULL по умолчанию в базе данных и вычислением сравнений со значениями NULL. Если параметр ANSI_NULLS включен (по умолчанию), то при проверке на наличие значений NULL в выражениях должен использоваться оператор IS NULL. Например, результатом следующего сравнения всегда является неизвестность при включенном параметре ANSI_NULLS:

colname > NULL 

Сравнение с переменной, содержащей значение NULL, также приводит к неизвестному результату:

colname > @MyVariable 

Для тестирования на значение NULL используются предикаты IS NULL и IS NOT NULL. Это может усложнить предложение WHERE. Например, столбец TerritoryID в таблице AdventureWorks Customer допускает значения NULL. Если инструкция SELECT используется для тестирования на значения NULL в дополнение к другим, она должна включать предикат IS NULL:

SELECT CustomerID, AccountNumber, TerritoryID FROM AdventureWorks.Sales.Customer WHERE TerritoryID IN (1, 2, 3) OR TerritoryID IS NULL 

Если в SQL Server параметр ANSI_NULLS отключен, можно создать выражения, которые используют оператор равенства для сравнения со значением NULL. Однако нельзя запретить другим подключениям задавать параметры NULL для этого подключения. Использование параметра IS NULL для проверки на наличие значений NULL всегда работает, независимо от установленного значения ANSI_NULLS для подключения.

Установка ANSI_NULLS OFF не поддерживается в DataSet , который всегда соответствует стандарту ANSI SQL-92 для обработки значений NULL в System.Data.SqlTypes.

Присвоение значений Null

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

В этом разделе описывается семантика значений NULL для присвоения значений NULL для DataColumn в DataRow в различных системах типов.

DBNull.Value
Это назначение допустимо для любого типа DataColumn . Если тип реализует INullable , DBNull.Value приводится к соответствующему строго типизированному значению NULL.

SqlType.Null
Все типы данных System.Data.SqlTypes реализуют INullable . Если строго типизированное значение NULL может быть преобразовано в тип данных столбца с помощью операторов неявного приведения, то назначение должно быть принятым. Иначе будет вызвано исключение недопустимого приведения.

null
Если значение NULL является допустимым для указанного типа данных DataColumn , оно приводится к соответствующему значению DbNull.Value или Null , связанному с типом INullable ( SqlType.Null ).

derivedUdt.Null
Для столбцов пользовательского типа значения NULL всегда хранятся в зависимости от типа, связанного с DataColumn . Рассмотрим случай пользовательского типа, связанного с DataColumn , который не реализует INullable в отличие от своего подкласса. В этом случае, если назначено строго типизированное значение NULL, связанное с производным классом, оно сохраняется как нетипизированное значение DbNull.Value , так как хранилище значений NULL всегда согласуется с типом данных DataColumn.

В настоящее время структура Nullable или Nullable не поддерживается в DataSet .

Значение по умолчанию для любого экземпляра System.Data.SqlTypes— NULL.

Значения NULL в System.Data.SqlTypes относятся к определенному типу и не могут быть представлены одним значением, таким как DbNull . Чтобы проверить на наличие значений NULL, используйте свойство IsNull .

Значения NULL могут быть назначены DataColumn, как показано в следующем примере кода. Вы можете напрямую назначить значения NULL для переменных SqlTypes без запуска исключения.

Пример

В следующем примере кода показано создание DataTable с двумя столбцами, определенными как SqlInt32 и SqlString. Код добавляет одну строку известных значений, одну строку значений NULL, а затем выполняет итерацию по DataTable, присваивая значения переменным и отображая выходные данные в окне консоли.

static void WorkWithSqlNulls() < DataTable table = new(); // Specify the SqlType for each column. DataColumn idColumn = table.Columns.Add("ID", typeof(SqlInt32)); DataColumn descColumn = table.Columns.Add("Description", typeof(SqlString)); // Add some data. DataRow nRow = table.NewRow(); nRow["ID"] = 123; nRow["Description"] = "Side Mirror"; table.Rows.Add(nRow); // Add null values. nRow = table.NewRow(); nRow["ID"] = SqlInt32.Null; nRow["Description"] = SqlString.Null; table.Rows.Add(nRow); // Initialize variables to use when // extracting the data. SqlBoolean isColumnNull = false; SqlInt32 idValue = SqlInt32.Zero; SqlString descriptionValue = SqlString.Null; // Iterate through the DataTable and display the values. foreach (DataRow row in table.Rows) < // Assign values to variables. Note that you // do not have to test for null values. idValue = (SqlInt32)row["ID"]; descriptionValue = (SqlString)row["Description"]; // Test for null value in ID column. isColumnNull = idValue.IsNull; // Display variable values in console window. Console.Write("isColumnNull=, Description=", isColumnNull, idValue, descriptionValue); Console.WriteLine(); > 

Private Sub WorkWithSqlNulls() Dim table As New DataTable() ' Specify the SqlType for each column. Dim idColumn As DataColumn = _ table.Columns.Add("ID", GetType(SqlInt32)) Dim descColumn As DataColumn = _ table.Columns.Add("Description", GetType(SqlString)) ' Add some data. Dim row As DataRow = table.NewRow() row("ID") = 123 row("Description") = "Side Mirror" table.Rows.Add(row) ' Add null values. row = table.NewRow() row("ID") = SqlInt32.Null row("Description") = SqlString.Null table.Rows.Add(row) ' Initialize variables to use when ' extracting the data. Dim isColumnNull As SqlBoolean = False Dim idValue As SqlInt32 = SqlInt32.Zero Dim descriptionValue As SqlString = SqlString.Null ' Iterate through the DataTable and display the values. For Each row In table.Rows ' Assign values to variables. Note that you ' do not have to test for null values. idValue = CType(row("ID"), SqlInt32) descriptionValue = CType(row("Description"), SqlString) ' Test for null value with ID column isColumnNull = idValue.IsNull ' Display variable values in console window. Console.Write("isColumnNull=, Description=", _ isColumnNull, idValue, descriptionValue) Console.WriteLine() Next row End Sub 

В этом примере отображаются следующие результаты:

isColumnNull=False, Description=Side Mirror isColumnNull=True, Description=Null 

Присвоение для многих столбцов или строк

DataTable.Add , DataTable.LoadDataRow или другие API-интерфейсы, принимающие массив ItemArray, который сопоставляется со строкой, сопоставляют значение NULL со значением по умолчанию DataColumn. Если объект в массиве содержит DbNull.Value или строго типизированный аналог, применяются те же правила, которые описаны выше.

Кроме того, следующие правила применяются к экземпляру назначений NULL DataRow.[«columnName»] :

1. Значение по умолчанию — DbNull.Value для всех, кроме строго типизированных столбцов NULL, где это соответствующее строго типизированное значение NULL.
2. Значения NULL никогда не записываются во время сериализации в XML-файлы (как в xsi:nil).
3. Все значения, в том числе по умолчанию, отличные от NULL, всегда записываются при сериализации в XML. Это отличается от семантики XSD/XML, где значение NULL (xsi: nil) является явным, а значение по умолчанию — неявным (если отсутствует в XML, то проверяющее средство синтаксического анализа может получить его из связанной схемы XSD). Обратное верно для DataTable : значение NULL является неявным, а значение по умолчанию — явным.
4. Всем отсутствующим значениям столбцов для строк, считываемых из входных данных XML, присваивается значение NULL. Строкам, созданным с помощью NewRow или аналогичных методов, присваивается значение по умолчанию DataColumn.
5. Метод IsNull возвращает true как для DbNull.Value , так и для INullable.Null .

Сравнение значений NULL с типами SqlType и CLR

При сравнении значений NULL важно понимать разницу между тем, как метод Equals вычисляет значения NULL в System.Data.SqlTypes по сравнению с тем, как он работает с типами CLR. Все методы System.Data.SqlTypes Equals используют семантику базы данных для вычисления значений NULL. Если одно или оба значения являются NULL, результатом сравнения будет NULL. С другой стороны, при использовании метода Equals CLR для двух System.Data.SqlTypes вернется значение true, если оба значения соответствуют NULL. Это отражает разницу между использованием метода экземпляра, такого как метод String.Equals CLR, и использованием статического или общего метода SqlString.Equals .

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

 static void CompareNulls() < // Create two new null strings. SqlString a = new(); SqlString b = new(); // Compare nulls using static/shared SqlString.Equals. Console.WriteLine("SqlString.Equals shared/static method:"); Console.WriteLine(" Two nulls=", SqlStringEquals(a, b)); // Compare nulls using instance method String.Equals. Console.WriteLine(); Console.WriteLine("String.Equals instance method:"); Console.WriteLine(" Two nulls=", StringEquals(a, b)); // Make them empty strings. a = ""; b = ""; // When comparing two empty strings (""), both the shared/static and // the instance Equals methods evaluate to true. Console.WriteLine(); Console.WriteLine("SqlString.Equals shared/static method:"); Console.WriteLine(" Two empty strings=", SqlStringEquals(a, b)); Console.WriteLine(); Console.WriteLine("String.Equals instance method:"); Console.WriteLine(" Two empty strings=", StringEquals(a, b)); > static string SqlStringEquals(SqlString string1, SqlString string2) < // SqlString.Equals uses database semantics for evaluating nulls. var returnValue = SqlString.Equals(string1, string2).ToString(); return returnValue; >static string StringEquals(SqlString string1, SqlString string2) < // String.Equals uses CLR type semantics for evaluating nulls. var returnValue = string1.Equals(string2).ToString(); return returnValue; >> 

Private Sub CompareNulls() ' Create two new null strings. Dim a As New SqlString Dim b As New SqlString ' Compare nulls using static/shared SqlString.Equals. Console.WriteLine("SqlString.Equals shared/static method:") Console.WriteLine(" Two nulls=", SqlStringEquals(a, b)) ' Compare nulls using instance method String.Equals. Console.WriteLine() Console.WriteLine("String.Equals instance method:") Console.WriteLine(" Two nulls=", StringEquals(a, b)) ' Make them empty strings. a = "" b = "" ' When comparing two empty strings (""), both the shared/static and ' the instance Equals methods evaluate to true. Console.WriteLine() Console.WriteLine("SqlString.Equals shared/static method:") Console.WriteLine(" Two empty strings=", SqlStringEquals(a, b)) Console.WriteLine() Console.WriteLine("String.Equals instance method:") Console.WriteLine(" Two empty strings=", StringEquals(a, b)) End Sub Private Function SqlStringEquals(ByVal string1 As SqlString, _ ByVal string2 As SqlString) As String ' SqlString.Equals uses database semantics for evaluating nulls. Dim returnValue As String = SqlString.Equals(string1, string2).ToString() Return returnValue End Function Private Function StringEquals(ByVal string1 As SqlString, _ ByVal string2 As SqlString) As String ' String.Equals uses CLR type semantics for evaluating nulls. Dim returnValue As String = string1.Equals(string2).ToString() Return returnValue End Function 

Получается следующий вывод:

SqlString.Equals shared/static method: Two nulls=Null String.Equals instance method: Two nulls=True SqlString.Equals shared/static method: Two empty strings=True String.Equals instance method: Two empty strings=True 

См. также

• Типы данных SQL Server и ADO.NET
• Общие сведения об ADO.NET

SQL-Ex blog

Столбцы, допускающие и не допускающие значения NULL, и добавление Not Null без ступора в PostgreSQL

Добавил Sergey Moiseenko on Среда, 7 июня. 2023

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

Начнем с определений. Термины Nullable и non-nullable для столбцов используются для описания возможности для столбца таблицы базы данных допускать или не допускать значения NULL. NULL означает неизвестные или отсутствующие данные. Это не то же самое, что пустая строка или число нуль. Например, вам требуется вставить адрес электронной почты контакта в таблицу. Если вы не знаете, имеет ли контакт электронную почту, вы можете вставить NULL в столбец электронного адреса. В этом случае NULL означает, что электронный адрес неизвестен. NULL ничему не равен, даже самому себе. Выражение ‘NULL == NULL’ возвращает ‘NULL’, поскольку два неизвестных значения не должны быть равными. Для проверки наличия значения ‘NULL’ вы используете логический оператор ‘IS NULL’. Оператор ниже вернет true для значения NULL или false в противном случае.

email_address IS NULL

Столбцы, не допускающие NULL-значения, имеют дополнительное ограничение, которое препятствует операциям INSERT или UPDATE вставку NULL. Если попытаться вставить NULL, в результате будет получена ошибка. Чтобы контролировать, может ли столбец принимать NULL, используется ограничение ‘NOT NULL’.

CREATE TABLE table_name( 
. 
имя_столбца тип_данных NOT NULL, 
. 
);

Добавление столбцов NOT NULL в новую таблицу

Оператор CREATE TABLE ниже создает новую таблицу с именем invoices и ограничением NOT NULL.

CREATE TABLE invoices( 
id SERIAL PRIMARY KEY, 
product_id INT NOT NULL, 
qty numeric NOT NULL CHECK(qty > 0), 
net_price numeric CHECK(net_price > 0) 
);

Если использовать NULL вместо NOT NULL, столбец будет принимать как NULL, так и не NULL значения. Если вы явно не укажите NULL или NOT NULL, столбец будет принимать NULL по умолчанию.

Добавление ограничения NOT NULL в существующий столбец

Ограничение NOT NULL добавляется в существующую таблицу с помощью следующего кода:

ALTER TABLE имя_таблицы 
ALTER COLUMN имя_столбца SET NOT NULL;

Проблемы при использовании столбцов, допускающих и не допускающих NULL-значения

Если вы явно не укажите ограничение NULL или NOT NULL, столбец будет принимать NULL по умолчанию. В результате допускающие NULL столбцы используются более широко, чем столбцы, не допускающие значения NULL. Имеется ряд недостатков при использовании допускающих NULL столбцов, особенно относящихся к производительности запросов. Когда столбец не допускает NULL значений, ядро базы данных может сделать определенные предположения относительно данных в этом столбце, что может привести к более быстрому выполнению запросов. Оптимизатор запросов знает, что столбец не может содержать NULL-значения, и может исключить специальные тесты на эти значения, подобные случаю сравнения NOT IN и NOT EXISTS.

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

Наибольший риск при добавлении не допускающих NULL столбцов заключается в том, чтобы сделать это без зависания работы приложения. Когда столбец имеет ограничение NOT NULL, таблица полностью переписывается, что является затратной операцией в зависимости от размера таблицы. Эта операция применяет блокировку таблицы, что препятствует операциям вставки и обновления, потенциально вызывая зависание работы приложения. Чтобы добавить не допускающие NULL столбцы в таблицу базы данных без замедления работы, вы можете использовать технологию, которая называется «онлайн-миграция схемы» («online schema migration»). Эта технология использует инструмент базы данных или скрипт для модификации схемы таблицы без блокирования таблицы или запрета операций чтения и записи.

Онлайн-миграция схемы

Давайте для примера возьмем простое приложения списка дел. Вы хотите позволить пользователям решить, большая это задача или маленькая.

1. Начнем с создания нового столбца в таблице с желаемым типом данных и ограничением NULL. Это позволит столбцу изначально принимать значения NULL.

ALTER TABLE `tasks` ADD COLUMN `type` VARCHAR(50) NULL;

2. Затем выполним оператор UPDATE для заполнения нового столбца данными. Это может быть сделано в пакетах, чтобы избежать влияния на производительность базы данных. Для этого примера мы обновим таблицу, установив для всех записей столбца type значение ‘small’ (маленькая). Это гарантирует, что все новые записи имеют надлежащий набор значений. Вы можете сделать это в одной транзакции с предыдущим шагом или в отдельной транзакции.

UPDATE tasks SET `type` = 'small' WHERE `type` IS NULL;

3. После заполнения нового столбца данными используем оператор ALTER TABLE для удаления ограничения NULL для столбца, делая его не допускающим значения NULL.

ALTER TABLE `tasks` CHANGE COLUMN `type` `type` VARCHAR(50) NOT NULL;

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

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

Вы можете вернуться к предыдущему состоянию, выполнив следующие действия:

1. Сделать столбец допускающим NULL-значения
2. Прекратить использование столбца в приложении
3. Удалить столбец из таблицы

Ограничение CHECK

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

ALTER TABLE foos 
add constraint id_not_null check (bar_id is not null) not valid;

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

После фиксации вышеприведенного оператора ALTER TABLE, вы можете выполнить

alter table foos validate constraint id_not_null;

Это не требует блокировки ACCESS EXCLUSIVE и по-прежнему разрешает доступ к таблице.

Заключение

Проектирование схемы базы данных является неотъемлемым компонентом проектирования системы. Допускать или не допускать значения NULL для столбцов являются базовым, но фундаментальным решением, которое влияет на целостность данных, производительность системы и время безотказной работы. В то время как ограничения NOT NULL улучшают производительность запросов и гарантируют целостность данных, их добавление в большие производственные базы может вызвать падение производительности, если вы не выполняете онлайн-миграцию схемы или не используете ограничение CHECK.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Использование значения NULL в условиях поиска

позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно).

Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:

Консоль

Выполнить

Характерной ошибкой является написание предиката в виде:

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

Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. пункт 5.10). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».

Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение (см. пункт 5.6):

Консоль

Выполнить

Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL -значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE (см. пункт 5.10):

Консоль

Выполнить

Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему:

А здесь мы получаем сравнение с NULL -значением, и в результате — UNKNOWN , что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание: