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 столбцы в таблицу базы данных без простоев или прерывания работы вашего приложения. Онлайн-миграция схемы является полезным методом для внесения изменений в схему в рабочей среде без нарушения доступности и падения производительности базы данных. Если что-то пойдет не так с новыми изменениями, вы легко можете вернуться к предыдущей версии приложения без каких-либо изменений структуры базы данных.
Вы можете вернуться к предыдущему состоянию, выполнив следующие действия:
- Сделать столбец допускающим NULL-значения
- Прекратить использование столбца в приложении
- Удалить столбец из таблицы
Ограничение 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.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
SQL условие IS NOT NULL
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
expression IS NOT NULL
Параметры или аргументы
expression Выражение для проверки значения NOT NULL.
Пример — использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Введите следующий SQL оператор:
FROM products
WHERE category_id IS NOT NULL ;
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Пример — использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий запрос UPDATE:
UPDATE customers
SET favorite_website = ‘google.com’
WHERE favorite_website IS NOT NULL ;
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
FROM customers;
Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | google.com |
| 6000 | Mila | Kunis | google.com |
| 7000 | Tom | Cruise | google.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
В этом примере будут обновлены все значения fav_website в таблице customer до google.com, где favourite_website содержит значение NULL. Как вы видите, значения поля favorite_website обновлены все строки кроме одной.
Пример — использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
В этом примере у нас есть таблица orders и следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий запрос DELETE:
SQL — Ограничение NOT NULL
По умолчанию столбец может содержать значения NULL. Ограничение NOT NULL приводит к тому, что столбец НЕ принимает значения NULL. Это приводит к тому, что поле всегда содержит значение, а это означает, что вы не можете вставить новую запись или обновить запись без добавления значения в это поле.
Следующий SQL гарантирует, что столбцы «user_id», «name» и «fullname» НЕ будут принимать значения NULL:
Пример:
CREATE TABLE users ( user_id int NOT NULL, name varchar(255) NOT NULL, fullname varchar(255) NOT NULL, balanse int );Ограничение NOT NULL в SQL
Ограничение NOT NULL означает, что столбец не может хранить значения NULL . Например:
CREATE TABLE Colleges (
college_id INT NOT NULL ,
college_code VARCHAR ( 20 ) ,
college_name VARCHAR ( 50 )
Здесь столбцы college_id и college_code таблицы Colleges не могут иметь значения NULL .
Примечание: Ключевые слова NOT NULL используются для добавления ограничений, тогда как условия IS NULL и IS NOT NULL используются с оператором WHERE для выбора строк из таблицы.
Удалить ограничение NOT NULL
Мы также можем удалить ограничение NOT NULL для столбца, если оно больше не нужно. Например:
SQL Server