SQL-Ex blog

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.