Реляционная модель данных и ограничения целостности

В теме 1 были названы основные свойства, которыми СУБД отличается от набора файлов: независимость данных, целостность, параллельный доступ, восстановление после сбоев. Там же говорилось, что реляционная модель Кодда предложила описывать данные как отношения и выражать запросы над ними декларативно ¹. Остаётся открытым вопрос, что именно такое «отношение», чем оно отличается от таблицы, которую создаёт CREATE TABLE, и каким образом база данных защищает себя от неверных данных: дубликатов покупателей, заказов несуществующих людей, книг с отрицательной ценой. Эта тема отвечает на первый вопрос определениями реляционной модели, а на второй — механизмом ограничений целостности (англ. integrity constraints).

Ограничение целостности — это утверждение о допустимых состояниях базы данных, которое СУБД проверяет при каждом изменении данных и, если утверждение нарушено, отвергает изменение. Ограничение действует независимо от того, какое приложение или какой человек вносит данные: ошибка в программе, забытая проверка в одном из десяти сервисов, ручной UPDATE администратора не могут обойти его. По этой причине правила предметной области, насколько это возможно, записываются в схему базы, а не в код приложений. Сам Кодд включил это требование в свой перечень признаков настоящей реляционной СУБД ²³.

Отдельного внимания заслуживает особое значение NULL. Оно позволяет хранить в строке «значение неизвестно» или «значение неприменимо», но платой за это становится трёхзначная логика: сравнение с NULL не истинно и не ложно, а «неизвестно». Из неё вытекают самые частые ошибки в запросах и самые неочевидные свойства ограничений, поэтому ей отведён отдельный раздел.

Изложение построено так. Первый раздел вводит понятия отношения, кортежа, домена и атрибута, показывает, где таблица SQL отступает от отношения, и кратко излагает правила Кодда. Второй раздел посвящён ключам и ссылочной целостности. Третий разбирает ограничения NOT NULL, UNIQUE, CHECK, EXCLUDE, действия при удалении и обновлении родительских строк и откладываемые проверки. Четвёртый раздел — NULL и трёхзначная логика с главной ловушкой, NOT IN. Пятый — язык определения данных и идентификаторы. Все примеры выполнены на PostgreSQL 16 и учебной базе «Книжный узел» (схема bookstore); отдельные демонстрации используют служебную схему demo, которая создаётся в ходе изложения. Запросы с соединениями (тема 4), проектирование схемы (темы 7–8) и выбор типов данных (тема 9) здесь не рассматриваются.

Отношения, кортежи и таблицы SQL

Домен, атрибут, кортеж, отношение

Реляционная модель строится на нескольких базовых понятиях, и все они имеют точные определения ¹⁴⁵.

Домен (англ. domain) — множество допустимых значений одного вида: целые числа, названия городов, коды ISBN из тринадцати цифр. Атрибут (англ. attribute) — именованная роль домена в отношении: атрибут city с доменом «названия городов» или атрибут birth_year с доменом «годы». Два атрибута разных отношений могут иметь один и тот же домен, и тогда их значения сравнимы: customer.city и warehouse.city — названия городов, а customer.city и book.price сравнивать бессмысленно, хотя технически в SQL это не всегда запрещено.

Схема отношения (англ. relation schema) — имя отношения и список его атрибутов с доменами. Для издательств учебной базы это

publisher(publisher_id : integer, name : text, city : text).

Кортеж (англ. tuple) над такой схемой — набор значений, по одному для каждого атрибута, взятому из его домена; в разговоре о SQL кортеж соответствует строке. Отношение (англ. relation) — конечное множество кортежей над одной схемой. Строгое определение связывает отношение с декартовым произведением доменов: подмножество D1 × D2 × … × Dn ¹. Другой, «именованный» вариант определения, более близкий практике, считает кортеж функцией, которая каждому имени атрибута ставит в соответствие значение, и тогда порядок атрибутов несуществен ⁴⁵. Число атрибутов называется степенью отношения (англ. degree, arity), число кортежей — мощностью (англ. cardinality). У отношения customer степень равна 5, мощность на масштабе scale=1 равна 5000.

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

Из определения отношения как множества вытекают три свойства, на которых строится вся теория:

• кортежи не повторяются (в множестве нет двух одинаковых элементов), поэтому любой кортеж можно адресовать его значениями;
• кортежи не упорядочены: понятия «первая строка» или «следующая» в отношении не существует;
• атрибуты различаются именами, а не позициями, и значение каждого атрибута атомарно (неделимо с точки зрения модели).

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

Небольшой пример из учебной базы. Три кортежа отношения publisher:

SELECT publisher_id, name, city
FROM publisher
WHERE publisher_id IN (1, 4, 10)
ORDER BY publisher_id;

 publisher_id |     name      |      city       
--------------+---------------+-----------------
            1 | Питер         | Санкт-Петербург
            4 | АСТ           | Москва
           10 | Северный узел | 
(3 rows)

Порядок строк здесь задан явно предложением ORDER BY, и это существенно: без него СУБД вправе вернуть те же строки в любом порядке (тема 3). У издательства «Северный узел» город не указан; отсутствующее значение в «чистой» модели Кодда 1970 года не предусмотрено, оно появилось позднее, и о нём речь пойдёт в разделе о NULL.

Чем таблица SQL отличается от отношения

Таблица в SQL — реализация отношения, но не точная. Различий несколько, и каждое объясняет какую-либо практическую особенность языка ⁵⁶.

Первое: таблица может содержать одинаковые строки. Если в таблице не объявлен ключ, СУБД не помешает вставить одну и ту же строку дважды, и запрос вернёт обе (в терминах темы 3 таблица SQL — мультимножество, англ. multiset, bag). Покажем это на служебной таблице без ограничений.

CREATE SCHEMA demo;
CREATE TABLE demo.dup (city text, n integer);
INSERT INTO demo.dup VALUES ('Казань', 1), ('Казань', 1), (NULL, 2), (NULL, 2);

SELECT * FROM demo.dup;

  city  | n 
--------+---
 Казань | 1
 Казань | 1
        | 2
        | 2
(4 rows)

Операция, которая превращает мультимножество в множество, — DISTINCT:

SELECT DISTINCT * FROM demo.dup;

  city  | n 
--------+---
 Казань | 1
        | 2
(2 rows)

Заметим, что две строки с NULL в city слились в одну: для DISTINCT (а также для GROUP BY и операций над множествами) два NULL считаются неразличимыми, хотя в условии WHERE сравнение NULL = NULL истинным не бывает. Эта двойственность разобрана в теме 3 и далее в этой теме.

Второе отличие: у столбцов таблицы есть порядок. SELECT * возвращает столбцы в том порядке, в котором они были созданы, а оператор INSERT INTO t VALUES (…) без списка столбцов опирается на эту позицию. В реляционной модели порядок атрибутов не существует, поэтому надёжный код всегда перечисляет столбцы явно: INSERT INTO t (a, b) VALUES (…). Третье: у строк таблицы тоже есть физический порядок (порядок хранения), но он не гарантирован и меняется при обновлениях и очистке; полагаться на него нельзя (тема 9).

Четвёртое и важнейшее: значением в строке SQL может быть NULL, которого нет в модели 1970 года. Появление NULL разрушает привычную двузначную логику и требует особых правил сравнения, о чём подробно — в четвёртом разделе.

Пятое: домены в SQL представлены типами данных, а собственно домен модели (множество допустимых значений вида «город», «год рождения») выражается сочетанием типа и ограничения CHECK. PostgreSQL умеет называть такое сочетание и применять его в нескольких таблицах — оператор CREATE DOMAIN:

CREATE DOMAIN demo.isbn13 AS char(13) CHECK (VALUE ~ '^[0-9]{13}$');
CREATE TABLE demo.shelf (isbn demo.isbn13);
INSERT INTO demo.shelf VALUES ('9785170878246');
INSERT INTO demo.shelf VALUES ('123');

ERROR:  value for domain isbn13 violates check constraint "isbn13_check"

Первая вставка принята, вторая отвергнута: правило хранится в одном месте и действует везде, где домен использован. В учебной базе домены не применяются, schema.sql повторяет проверку формата ISBN в самой таблице book. Выбор типов (integer, numeric, text, timestamptz) — тема 9.

Из различий вытекает практический вывод. Реляционная теория справедлива для таблиц SQL ровно настолько, насколько мы сами возвращаем им свойства отношения: объявляем ключи, запрещающие дубликаты, не опираемся на порядок строк и столбцов, осознанно обращаемся с NULL. Критики SQL, в первую очередь Дейт и Дарвен, считают именно эти отступления главными дефектами языка и ищут им замену ⁷; для практики же важно, что ограничения целостности — это инструмент, возвращающий таблице строгость отношения.

Правила Кодда

Через пятнадцать лет после первой статьи Кодд столкнулся с тем, что слово «реляционная» стало маркетинговой этикеткой: продукты, всего лишь показывавшие данные в виде таблиц, объявлялись реляционными. В октябре 1985 года он опубликовал в еженедельнике Computerworld две статьи — «Is Your DBMS Really Relational?» и «Does Your DBMS Run By the Rules?» — с перечнем тринадцати правил (нумерация от 0 до 12), которым должна удовлетворять система, чтобы называться полностью реляционной ²³. Перечислим их; формулировки правил 1, 2, 3 и 10 приведены по оригиналу, остальные пересказаны близко к тексту. Перевод наш.

Как относиться к этому перечню? Правила 8 и 9 уже знакомы из темы 1 как физическая и логическая независимость данных, правило 4 реализовано системным каталогом pg_catalog и information_schema (в третьем разделе мы будем читать его запросами), правило 12 требует, чтобы никакой низкоуровневый интерфейс (например, прямая запись в файлы данных) не позволял обойти проверки, которые заданы на языке схемы. Правила 3 и 10 непосредственно относятся к теме: первое требует единого NULL, не зависящего от типа, а второе — чтобы ограничения жили в схеме, а не в приложениях. Другие правила, например 6 и 11, реализованы частично (обновляемые представления — тема 6, распределённость — тема 16).

Поправим два распространённых заблуждения. Правила Кодда не определяют SQL: он в них даже не упомянут, а сам SQL, по оценке Дейта, отступает от реляционной модели (допускает дубликаты строк и опирается на порядок столбцов) ⁴⁷. Кроме того, принято считать, что ни одна коммерческая СУБД не удовлетворяет всем правилам полностью, включая PostgreSQL: перечень служит ориентиром и мерой «реляционности», а не стандартом, по которому выдаётся сертификат.

Ключи и ссылочная целостность

Суперключ, потенциальный и первичный ключи

Чтобы адресовать кортеж значениями, нужно выбрать атрибуты, по которым он однозначно находится. Суперключ (англ. superkey) отношения — любой набор атрибутов, значения которого различны у любых двух кортежей отношения. Такой набор всегда существует: например, все атрибуты вместе, поскольку кортежи отношения не повторяются. Потенциальный ключ (англ. candidate key) — минимальный суперключ, то есть суперключ, из которого нельзя убрать ни одного атрибута без потери уникальности. Первичный ключ (англ. primary key) — один потенциальный ключ, выбранный в качестве основного способа идентификации; остальные потенциальные ключи называются альтернативными (англ. alternate keys). Первичный ключ в реляционной модели не может содержать неопределённых значений: это требование целостности сущности (англ. entity integrity), на нём держится гарантия, что любой кортеж найдётся по ключу ¹⁴.

Различие между суперключом и потенциальным ключом легко видеть на таблице customer. Атрибут customer_id — потенциальный ключ; email — тоже потенциальный ключ (адрес уникален); пара (customer_id, email) — суперключ, но не потенциальный ключ, так как каждый из двух атрибутов уже сам различает строки. Ключ может быть составным: у таблицы order_item он состоит из двух атрибутов, (order_id, book_id), потому что один заказ содержит много книг, а одна книга встречается во многих заказах, и только пара определяет позицию.

Ключ — это свойство смысла предметной области, а не данных. Данные способны только опровергнуть гипотезу о ключе, подтвердить её они не могут: если в сегодняшнем экземпляре все значения различны, завтра может появиться повтор. Проверим несколько гипотез о ключах на данных учебной базы.

SELECT count(*)                          AS cardinality,
       count(DISTINCT email)             AS emails,
       count(DISTINCT full_name)         AS names,
       count(DISTINCT (full_name, city)) AS names_city
FROM customer;

 cardinality | emails | names | names_city 
-------------+--------+-------+------------
        5000 |   5000 |    80 |        639
(1 row)

Адрес почты различает всех пяти тысяч покупателей, полное имя — нет (80 различных имён на 5000 человек), и добавление города ключом пару не делает (639 различных пар). Для авторов даже содержательный набор «имя и год рождения» не гарантирует уникальности:

SELECT author_id, full_name, birth_year, country
FROM author
WHERE full_name = 'Андрей Смирнов'
ORDER BY author_id;

 author_id |   full_name    | birth_year | country  
-----------+----------------+------------+----------
        25 | Андрей Смирнов |       1959 | Германия
        98 | Андрей Смирнов |       1937 | Россия
       196 | Андрей Смирнов |       1971 | Россия
(3 rows)

В базе три разных автора с одинаковым именем, и среди 210 авторов различных имён лишь 155. Поэтому таблице author нужен ключ, не связанный со смыслом полей, — суррогатный.

Естественный и суррогатный ключи

Естественный ключ (англ. natural key) составлен из атрибутов, имеющих смысл в предметной области: ISBN книги, адрес электронной почты, паспортные данные. Суррогатный ключ (англ. surrogate key) — искусственный идентификатор без смысла, который выдаёт сама база: целое число из последовательности или UUID. В учебной базе у каждой таблицы есть суррогатный первичный ключ ..._id, а естественные ключи (isbn, email, название издательства, город склада) объявлены альтернативными через UNIQUE.

У каждого варианта свои плюсы и издержки, и выбор между ними — компромисс.

• Естественные ключи понятны человеку и сами входят в бизнес-процессы, но они меняются (человек сменил адрес почты, издательство переименовалось), бывают составными и длинными, а иногда оказываются не такими уникальными, как казалось (повторные ISBN у переизданий, одинаковые имена авторов). Каждое изменение естественного ключа обязано распространиться на все ссылающиеся строки (ON UPDATE CASCADE, см. ниже).
• Суррогатные ключи неизменны, компактны (4 байта для integer против десятков байтов строки в каждом индексе и каждой дочерней таблице) и однородны, но не защищают от логических дубликатов: если для таблицы выбран только суррогатный ключ, то один и тот же покупатель может быть вставлен дважды под разными номерами. Поэтому суррогатный первичный ключ почти всегда сопровождается UNIQUE на естественном ключе, как в customer.
• Суррогатные ключи, в свою очередь, раскрывают информацию, если выдаются подряд (внешний наблюдатель видит число заказов), и требуют решения, как их генерировать в распределённой системе (тема 16).

Выбор типа и генерации суррогата (integer, bigint, uuid) относится к теме 9, а правила, как найти ключи по функциональным зависимостям, — к теме 8; здесь важно только различие по смыслу.

Внешний ключ и ссылочная целостность

Внешний ключ (англ. foreign key) — набор атрибутов одного отношения (дочернего), значения которого обязаны совпадать со значениями потенциального ключа другого (родительского) отношения или быть неопределёнными. Требование называется ссылочной целостностью (англ. referential integrity) ¹⁴. Так, book.publisher_id ссылается на publisher.publisher_id: нельзя завести книгу издательства, которого не существует. Внешний ключ превращает набор отдельных таблиц в связную схему — ссылки между отношениями выражаются только значениями, без указателей, и это та самая «единственность представления» из информационного правила Кодда.

В SQL внешний ключ объявляется предложением REFERENCES в определении столбца или FOREIGN KEY на уровне таблицы. Приведём фрагмент определения таблицы orders:

CREATE TABLE orders (
    order_id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  integer NOT NULL REFERENCES customer (customer_id),
    warehouse_id integer REFERENCES warehouse (warehouse_id),
    ...
);

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

Во-первых, внешний ключ может быть пустым: warehouse_id не объявлен NOT NULL, и заказ без склада допустим (склад ещё не назначен). Правило «значение либо существует в родителе, либо неопределённо» для составного внешнего ключа в PostgreSQL по умолчанию действует в варианте MATCH SIMPLE: проверка пропускается, если хотя бы один столбец ключа равен NULL. Это легко проверить на двухстолбцовом ключе.

CREATE TABLE demo.cp (a integer, b integer, PRIMARY KEY (a, b));
CREATE TABLE demo.cc (id integer, a integer, b integer, FOREIGN KEY (a, b) REFERENCES demo.cp);
INSERT INTO demo.cc VALUES (1, 5, NULL);
INSERT INTO demo.cc VALUES (2, 5, 6);

INSERT 0 1
ERROR:  insert or update on table "cc" violates foreign key constraint "cc_a_b_fkey"
DETAIL:  Key (a, b)=(5, 6) is not present in table "cp".

Строка со значением (5, NULL) принята, хотя в таблице cp нет ни одной строки с a = 5. Вариант MATCH FULL запрещает частично заполненные ключи (либо все столбцы NULL, либо ни один) ⁹. Чтобы не получать таких «дыр», столбцы составного внешнего ключа обычно объявляют NOT NULL.

Во-вторых, внешний ключ обязан ссылаться на столбцы, на которых есть ограничение PRIMARY KEY или UNIQUE. Иначе ссылка была бы неоднозначной:

CREATE TABLE demo.nu (code text);
CREATE TABLE demo.nuc (c text REFERENCES demo.nu (code));

ERROR:  there is no unique constraint matching given keys for referenced table "nu"

Внешний ключ проверяется с двух сторон. Вставка или обновление дочерней строки требует, чтобы родитель существовал. Удаление или изменение ключа родителя, на который ещё ссылаются, должно быть либо запрещено, либо сопровождено действием над дочерними строками; набор действий рассмотрен в третьем разделе. Сколько в учебной базе внешних ключей и как они устроены, можно узнать из системного каталога:

SELECT conrelid::regclass AS child, pg_get_constraintdef(oid) AS fk
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid IN ('orders'::regclass, 'order_item'::regclass, 'payment'::regclass)
ORDER BY 1, 2;

   child    |                                  fk                                  
------------+----------------------------------------------------------------------
 orders     | FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
 orders     | FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id)
 order_item | FOREIGN KEY (book_id) REFERENCES book(book_id)
 order_item | FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
 payment    | FOREIGN KEY (order_id) REFERENCES orders(order_id)
(5 rows)

Всего в схеме bookstore 14 внешних ключей; при удалении родителя каскадно ведут себя только два: book_author.book_id и order_item.order_id. Остальные запрещают удаление, пока на строку есть ссылки. Одно предупреждение для будущих тем. PostgreSQL автоматически создаёт индекс для первичного ключа и для UNIQUE, но не для внешнего ключа: в учебной базе индексов по внешним ключам намеренно нет, и вопрос о том, нужны ли они, решается в теме 10.

Чтобы убедиться, что данные удовлетворяют ссылочной целостности, например в базе, куда загружали данные в обход ограничений, ищут «сирот» — дочерние строки без родителя:

SELECT count(*) AS orphans
FROM book b
WHERE b.publisher_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM publisher p WHERE p.publisher_id = b.publisher_id);

 orphans 
---------
       0
(1 row)

Условие publisher_id IS NOT NULL существенно: книги без издательства (таких 95) сиротами не являются. Подзапрос с NOT EXISTS разбирается в теме 4; в разделе о NULL мы увидим, почему здесь нельзя заменить его на NOT IN. В части 5 лабораторной работы такие запросы нужно написать для базы с испорченными данными.

Ограничения целостности в SQL

Виды ограничений и момент проверки

Ограничения принято группировать по тому, что они защищают ⁴⁵.

• Ограничения домена: допустимы ли значения атрибута (тип, NOT NULL, CHECK над одним столбцом, домен).
• Целостность сущности: первичный ключ не пуст и уникален.
• Ссылочная целостность: внешний ключ указывает на существующую строку.
• Семантические ограничения (бизнес-правила): условия над несколькими столбцами строки (CHECK), над несколькими строками (уникальность, исключающие ограничения) и, в общем случае, над всей базой.

Последняя группа в стандарте SQL представлена утверждениями (англ. assertions, CREATE ASSERTION) — произвольными логическими условиями над базой. Реализовать их эффективно трудно, и PostgreSQL их не поддерживает ⁸; правила, выходящие за рамки перечисленных ограничений, в PostgreSQL выражают триггерами (тема 14) или проверяют в приложении, в обоих случаях принимая на себя заботу о гонках между параллельными транзакциями (тема 12).

С точки зрения синтаксиса ограничение бывает столбцовым (записывается в определении столбца и относится к нему) и табличным (записывается отдельно и может охватывать несколько столбцов); ему можно дать имя предложением CONSTRAINT имя. Имя стоит давать всегда: если его не задать, PostgreSQL придумает собственное (points_tx_check, book_pages_check), а именно по имени ограничение упоминается в сообщении об ошибке, в ALTER TABLE … DROP CONSTRAINT и в журнале сервера.

В схеме bookstore определено 12 ограничений первичных ключей, 6 ограничений UNIQUE, 14 внешних ключей и 12 ограничений CHECK (ограничения NOT NULL PostgreSQL 16 хранит не в pg_constraint, а как свойство столбца; тема 1):

SELECT contype, count(*)
FROM pg_constraint
WHERE connamespace = 'bookstore'::regnamespace
GROUP BY contype
ORDER BY contype;

 contype | count 
---------+-------
 c       |    12
 f       |    14
 p       |    12
 u       |     6
(4 rows)

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

NOT NULL, UNIQUE, CHECK, EXCLUDE

Ограничение NOT NULL запрещает столбцу принимать неопределённое значение. Первичный ключ включает его автоматически:

CREATE TABLE demo.p1 (id integer PRIMARY KEY);
INSERT INTO demo.p1 VALUES (NULL);

ERROR:  null value in column "id" of relation "p1" violates not-null constraint
DETAIL:  Failing row contains (null).

Ограничение UNIQUE запрещает двум строкам иметь одинаковое значение в столбце или наборе столбцов, но трёхзначная логика вносит оговорку: два NULL не считаются равными, поэтому строки, у которых в ограничиваемых столбцах есть NULL, друг другу не противоречат. Проверим на таблице с уникальным кодом:

CREATE TABLE demo.u1 (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code text UNIQUE);
INSERT INTO demo.u1 (code) VALUES ('a'), (NULL), (NULL);
SELECT * FROM demo.u1;
INSERT INTO demo.u1 (code) VALUES ('a');

 id | code 
----+------
  1 | a
  2 | 
  3 | 
(3 rows)

ERROR:  duplicate key value violates unique constraint "u1_code_key"
DETAIL:  Key (code)=(a) already exists.

Два NULL в столбце code приняты, дубль значения a отвергнут. Это стандартное поведение: ограничение выполнено, если никакие две строки не имеют равных определённых значений. У составного ограничения достаточно одного NULL, чтобы строка стала «уникальной»: две строки (1, NULL) в таблице с UNIQUE (a, b) допустимы. В PostgreSQL 15 появился вариант, который считает NULL равными друг другу, UNIQUE NULLS NOT DISTINCT ⁹:

CREATE TABLE demo.u2 (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code text UNIQUE NULLS NOT DISTINCT
);
INSERT INTO demo.u2 (code) VALUES ('a'), (NULL);
INSERT INTO demo.u2 (code) VALUES (NULL);

ERROR:  duplicate key value violates unique constraint "u2_code_key"
DETAIL:  Key (code)=(null) already exists.

Первые две строки прошли, а второй NULL отвергнут. Какой вариант выбрать, определяет смысл NULL в столбце. Для необязательного идентификатора (ISBN книги, у которой его нет) нужен обычный UNIQUE: книг без ISBN может быть сколько угодно. Для атрибута, у которого «не указано» само считается значением (скажем, «глобальная настройка», у которой нет владельца, и она должна быть единственной), подходит NULLS NOT DISTINCT. Поведение обычного UNIQUE различается между СУБД (например, Microsoft SQL Server допускает в таком столбце лишь один NULL), поэтому переносимые схемы на него не опираются.

Ограничение CHECK содержит логическое выражение над значениями строки; строка допускается, если выражение не ложно. Это важнейшее отличие от WHERE, который оставляет строку, только если условие истинно: при результате «неизвестно» CHECK пропускает строку.

CREATE TABLE demo.c1 (x integer CHECK (x > 0));
INSERT INTO demo.c1 VALUES (NULL);
INSERT INTO demo.c1 VALUES (-1);

INSERT 0 1
ERROR:  new row for relation "c1" violates check constraint "c1_x_check"
DETAIL:  Failing row contains (-1).

Значение NULL проходит проверку x > 0, потому что NULL > 0 равно NULL, а не ложь. Если NULL недопустим, нужен отдельный NOT NULL; именно так и устроены price numeric(10, 2) NOT NULL CHECK (price >= 0) в таблице book и остальные проверки учебной схемы. CHECK может связывать несколько столбцов строки, например CHECK (reason <> 'purchase' OR order_id IS NOT NULL) («покупка обязана ссылаться на заказ»; это импликация «если покупка, то заказ указан», записанная через OR). Ограничение не может содержать подзапрос или обращаться к другим строкам:

CREATE TABLE demo.sc (x integer CHECK (x IN (SELECT book_id FROM bookstore.book)));

ERROR:  cannot use subquery in check constraint

Проверка «значение есть в другой таблице» — это внешний ключ, а не CHECK. Заметим также, что PostgreSQL проверяет CHECK только при вставке и обновлении строки и считает выражение неизменным: функции с побочными эффектами или зависящие от времени (now()) в нём недопустимы по смыслу, хотя синтаксис их иногда пропускает.

Исключающее ограничение (англ. exclusion constraint, EXCLUDE) обобщает UNIQUE: оно запрещает двум строкам одновременно удовлетворять заданному оператору сравнения по заданным выражениям. Классический пример — периоды действия акции на книгу, которые не должны пересекаться. Ограничение строится на индексе GiST (тема 10), а для сравнения обычных целых на равенство нужно расширение btree_gist из набора contrib:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE demo.promo (
    book_id integer,
    period  daterange,
    EXCLUDE USING gist (book_id WITH =, period WITH &&)
);
INSERT INTO demo.promo VALUES (1, '[2026-01-01,2026-02-01)');
INSERT INTO demo.promo VALUES (1, '[2026-01-15,2026-03-01)');
INSERT INTO demo.promo VALUES (1, '[2026-02-01,2026-03-01)');

INSERT 0 1
ERROR:  conflicting key value violates exclusion constraint "promo_book_id_period_excl"
DETAIL:  Key (book_id, period)=(1, [2026-01-15,2026-03-01)) conflicts with existing key (book_id, period)=(1, [2026-01-01,2026-02-01)).
INSERT 0 1

Вторая акция пересекается с первой по оператору && («имеют общие точки») и отвергнута, третья начинается там, где первая закончилась (правая граница интервала исключена), и принята. Выражение такого правила в виде UNIQUE невозможно; в стандарте SQL аналога нет, это расширение PostgreSQL. Подробности типов диапазонов — тема 9.

Действия при удалении и обновлении родительской строки

Если на родительскую строку ссылаются, ее нельзя просто удалить: дочерние строки стали бы сиротами. Для этого случая в определении внешнего ключа задают действие над дочерними строками ⁹:

Действия задаются отдельно для удаления и для обновления: ON DELETE … и ON UPDATE …. Проверим все четыре «активных» варианта на одинаковых данных:

CREATE TABLE demo.parent (id integer PRIMARY KEY);
CREATE TABLE demo.ch_restrict (id integer PRIMARY KEY, p integer REFERENCES demo.parent ON DELETE RESTRICT);
CREATE TABLE demo.ch_cascade  (id integer PRIMARY KEY, p integer REFERENCES demo.parent ON DELETE CASCADE);
CREATE TABLE demo.ch_setnull  (id integer PRIMARY KEY, p integer REFERENCES demo.parent ON DELETE SET NULL);
CREATE TABLE demo.ch_setdef   (id integer PRIMARY KEY, p integer DEFAULT 0 REFERENCES demo.parent ON DELETE SET DEFAULT);

INSERT INTO demo.parent VALUES (0), (1), (2);
INSERT INTO demo.ch_restrict VALUES (1, 1);
INSERT INTO demo.ch_cascade  VALUES (1, 1);
INSERT INTO demo.ch_setnull  VALUES (1, 1);
INSERT INTO demo.ch_setdef   VALUES (1, 1);

DELETE FROM demo.parent WHERE id = 1;

ERROR:  update or delete on table "parent" violates foreign key constraint "ch_restrict_p_fkey" on table "ch_restrict"
DETAIL:  Key (id)=(1) is still referenced from table "ch_restrict".

Оператор отвергнут целиком, потому что одна из дочерних таблиц запрещает удаление (RESTRICT); остальные три действия при этом не сработали. Уберём «запрещающую» строку и повторим:

DELETE FROM demo.ch_restrict;
DELETE FROM demo.parent WHERE id = 1;
SELECT 'cascade' AS child, count(*) FROM demo.ch_cascade
UNION ALL SELECT 'setnull', count(*) FROM demo.ch_setnull;
SELECT p FROM demo.ch_setnull;
SELECT p FROM demo.ch_setdef;

  child  | count 
---------+-------
 cascade |     0
 setnull |     1
(2 rows)

 p 
---
  
(1 row)

 p 
---
 0
(1 row)

Каскадная дочерняя строка удалена; в ch_setnull строка осталась, но ссылка в ней теперь NULL; в ch_setdef ссылка переключилась на значение по умолчанию 0 (родитель 0 существовал). Выбор действия — проектное решение. Позиции заказа не имеют смысла без заказа, поэтому order_item.order_id объявлен с ON DELETE CASCADE; платёж не должен бесследно исчезать вместе с заказом, поэтому у payment.order_id каскада нет, и заказ, по которому есть платёж, удалить нельзя. CASCADE удобен, но опасен: одна команда способна удалить цепочку таблиц, и подобные последствия нужно держать в голове до выполнения DELETE. Различие между NO ACTION и RESTRICT проявляется только при откладываемых ограничениях и показано в следующем подразделе.

Действие ON UPDATE CASCADE оправдано при естественных ключах, значение которых может меняться:

CREATE TABLE demo.nk  (code text PRIMARY KEY);
CREATE TABLE demo.nkc (c text REFERENCES demo.nk ON UPDATE CASCADE);
INSERT INTO demo.nk VALUES ('A');
INSERT INTO demo.nkc VALUES ('A');
UPDATE demo.nk SET code = 'B';
SELECT * FROM demo.nkc;

 c 
---
 B
(1 row)

Суррогатные ключи, значение которых не меняется, делают такое действие ненужным.

Откладываемые ограничения

По умолчанию ограничение проверяется после каждого оператора. Для внешнего ключа это означает, что порядок вставки в нескольких таблицах строго определён: сначала родитель, потом потомок. Но бывают схемы, где такого порядка нет. Пусть у отдела есть начальник, а у сотрудника отдел, и оба столбца обязательны:

CREATE TABLE demo.emp  (emp_id integer PRIMARY KEY, dept_id integer NOT NULL);
CREATE TABLE demo.dept (
    dept_id integer PRIMARY KEY,
    head_id integer NOT NULL REFERENCES demo.emp (emp_id) DEFERRABLE INITIALLY DEFERRED
);
ALTER TABLE demo.emp ADD FOREIGN KEY (dept_id) REFERENCES demo.dept (dept_id) DEFERRABLE INITIALLY DEFERRED;

Цикл ссылок emp → dept → emp нельзя заполнить обычными ограничениями: сотрудника нельзя вставить без отдела, а отдел — без начальника. Выход даёт откладываемое ограничение (англ. deferrable constraint): пока транзакция не завершена, проверка может быть отложена до момента фиксации COMMIT. Для этого ограничение объявляют DEFERRABLE, а режим по умолчанию — INITIALLY DEFERRED (отложено) или INITIALLY IMMEDIATE (немедленно, но допускает откладывание в конкретной транзакции) ⁹¹⁰. Откладывать можно проверку UNIQUE, PRIMARY KEY, EXCLUDE и внешних ключей, но не NOT NULL и не CHECK.

Попробуем вставить строки без явной транзакции:

INSERT INTO demo.emp VALUES (1, 10);

ERROR:  insert or update on table "emp" violates foreign key constraint "emp_dept_id_fkey"
DETAIL:  Key (dept_id)=(10) is not present in table "dept".

Ошибка возникает, хотя ограничение объявлено отложенным: вне блока BEGIN … COMMIT каждый оператор является отдельной транзакцией, и его «конец транзакции» наступает сразу. Откладывание имеет смысл только внутри явной транзакции:

BEGIN;
INSERT INTO demo.emp VALUES (1, 10);
INSERT INTO demo.dept VALUES (10, 1);
COMMIT;

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

Обе вставки приняты: на момент COMMIT оба ограничения выполнены. Если к моменту фиксации они нарушены, ошибка возникает на COMMIT, а вся транзакция откатывается:

BEGIN;
INSERT INTO demo.emp VALUES (2, 20);
COMMIT;

BEGIN
INSERT 0 1
ERROR:  insert or update on table "emp" violates foreign key constraint "emp_dept_id_fkey"
DETAIL:  Key (dept_id)=(20) is not present in table "dept".

Сообщение об ошибке пришло на COMMIT: оператор INSERT отработал, а нарушение обнаружилось при фиксации. Командой SET CONSTRAINTS ALL IMMEDIATE (или по имени) проверку можно запросить немедленно, а SET CONSTRAINTS … DEFERRED — наоборот, отложить откладываемые ограничения с режимом INITIALLY IMMEDIATE ¹⁰. Какие ограничения откладываемы и в каком режиме, видно в каталоге:

SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid IN ('demo.emp'::regclass, 'demo.dept'::regclass) AND contype = 'f';

      conname      | condeferrable | condeferred 
-------------------+---------------+-------------
 dept_head_id_fkey | t             | t
 emp_dept_id_fkey  | t             | t
(2 rows)

Второе применение откладываемых ограничений — UNIQUE. Оператор UPDATE seq SET n = n + 1, который сдвигает все значения уникального столбца на единицу, логически корректен (после оператора значения снова различны), но PostgreSQL проверяет обычное неоткладываемое ограничение сразу по каждой строке и может отвергнуть его на промежуточном состоянии:

CREATE TABLE demo.seq  (n integer UNIQUE);
INSERT INTO demo.seq VALUES (1), (2), (3);
UPDATE demo.seq SET n = n + 1;

ERROR:  duplicate key value violates unique constraint "seq_n_key"
DETAIL:  Key (n)=(2) already exists.

Стандарт SQL требует проверять уникальность в конце оператора, и чтобы получить такое поведение в PostgreSQL, ограничение объявляют DEFERRABLE (хотя бы INITIALLY IMMEDIATE) ⁹. На таблице n integer UNIQUE DEFERRABLE INITIALLY IMMEDIATE тот же оператор выполняется (UPDATE 3). Важное замечание: порядок обработки строк в оператор не определён, поэтому ошибка в примере выше может возникнуть или не возникнуть в зависимости от физического порядка строк.

Наконец, посмотрим, чем NO ACTION отличается от RESTRICT. Заведём две дочерние таблицы с откладываемыми внешними ключами на одну родительскую и в одной транзакции удалим родителя и вернём его обратно:

CREATE TABLE demo.pa (id integer PRIMARY KEY);
CREATE TABLE demo.ca (p integer REFERENCES demo.pa ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE demo.cr (p integer REFERENCES demo.pa ON DELETE RESTRICT  DEFERRABLE INITIALLY DEFERRED);
INSERT INTO demo.pa VALUES (1);
INSERT INTO demo.ca VALUES (1);

BEGIN; DELETE FROM demo.pa WHERE id = 1; INSERT INTO demo.pa VALUES (1); COMMIT;

В таблице ca (NO ACTION) эта последовательность завершается успешно: к моменту фиксации родитель снова существует. Теперь очистим ca, вставим строку в cr (RESTRICT) и повторим транзакцию:

BEGIN
ERROR:  update or delete on table "pa" violates foreign key constraint "cr_p_fkey" on table "cr"
DETAIL:  Key (id)=(1) is still referenced from table "cr".

Действие RESTRICT срабатывает немедленно, невзирая на DEFERRABLE: удаление родителя отвергнуто, хотя к концу транзакции всё было бы восстановлено. Практическое правило: если ограничение должно откладываться, оставляют NO ACTION.

NULL и трёхзначная логика

Что означает NULL

В реальных данных значение часто неизвестно или не имеет смысла. Издательство «Северный узел» есть в справочнике, но его город мы не знаем; у книги Кнута «Искусство программирования» в учебной базе издательство не указано; у покупателя может не быть заполнен город. Кодд назвал два разных случая: информация отсутствует (значение существует, но неизвестно) и информация неприменима (значения нет по смыслу, например «девичья фамилия» у мужчины) ²³. В SQL для обоих служит одно и то же значение NULL (англ. null), и по правилу 3 оно должно быть отлично от пустой строки и от нуля.

NULL не является значением домена: это отметка «значения нет», которую допускает любой тип. Следовательно, '' IS NULL ложно (пустая строка — определённое значение, текст нулевой длины), а 0 IS NULL тоже ложно. В отдельных СУБД это не так: Oracle считает пустую строку NULL, и перенос запросов между системами из-за этого требует внимания. Поскольку NULL выражает неопределённость, любая операция, один из аргументов которой неопределён, неопределённа: NULL + 1 и 'abc' || NULL дают NULL. Единый NULL для двух разных смыслов — одна из причин, по которым Дейт и Дарвен критикуют SQL и предлагают вообще обойтись без NULL, моделируя необязательные свойства отдельными таблицами ⁷. Практический вывод для проектирования: столбец следует объявлять NOT NULL, если значение в предметной области обязательно, а NULL допускать, только когда «неизвестно» или «неприменимо» осмысленно. Разделение смыслов, если оно нужно, делают явным отдельным столбцом (статус, причина отсутствия), а не магическим значением вроде 0 или '—': Кодд называл такие приёмы несистематичными, и по ним трудно считать итоги.

Таблицы истинности

Сравнение с неопределённым значением не может быть ни истинным, ни ложным: ответ на вопрос «равен ли неизвестный город Казани?» — «неизвестно». SQL вводит третье логическое значение UNKNOWN (в PostgreSQL оно представлено значением NULL типа boolean), и все логические операции определены для трёх значений. Эта система называется трёхзначной логикой Клини (англ. Kleene's three-valued logic). Идея проста: неизвестное значение могло бы быть как истиной, так и ложью, и результат определён лишь тогда, когда он одинаков в обоих случаях. Поэтому FALSE AND UNKNOWN — ложь (что бы ни скрывалось за неизвестным, конъюнкция с ложью ложна), а TRUE AND UNKNOWN — неизвестно. Получим все таблицы запросом, а не из справочника:

SELECT a AS "A", b AS "B", a AND b AS "A AND B", a OR b AS "A OR B"
FROM (VALUES (true), (false), (NULL::boolean)) x(a)
CROSS JOIN (VALUES (true), (false), (NULL::boolean)) y(b);

  A   |  B   | A AND B | A OR B 
------+------+---------+--------
 t    | t    | t       | t
 t    | f    | f       | t
 t    | NULL | NULL    | t
 f    | t    | f       | t
 f    | f    | f       | f
 f    | NULL | f       | NULL
 NULL | t    | NULL    | t
 NULL | f    | f       | NULL
 NULL | NULL | NULL    | NULL
(9 rows)

(В этом выводе psql запущен с \pset null NULL, чтобы неопределённое значение было видно; по умолчанию оно печатается пустым.) Прочитаем таблицу: AND даёт ложь, если хотя бы один операнд ложен, и неизвестно, если ни один не ложен, но один неизвестен; OR даёт истину, если хотя бы один операнд истинен, и неизвестно, если ни один не истинен, но один неизвестен. Конструкция CROSS JOIN (декартово произведение) знакома по теме 3, соединения в SQL — тема 4. Отрицание и четыре предиката проверки состояния:

SELECT a AS "A", NOT a AS "NOT A", a IS NULL AS "IS NULL", a IS TRUE AS "IS TRUE",
       a IS NOT TRUE AS "IS NOT TRUE", a IS UNKNOWN AS "IS UNKNOWN"
FROM (VALUES (true), (false), (NULL::boolean)) x(a);

  A   | NOT A | IS NULL | IS TRUE | IS NOT TRUE | IS UNKNOWN 
------+-------+---------+---------+-------------+------------
 t    | f     | f       | t       | f           | f
 f    | t     | f       | f       | t           | f
 NULL | NULL  | t       | f       | t           | t
(3 rows)

NOT UNKNOWN — тоже неизвестно. Предикаты IS NULL, IS TRUE, IS NOT TRUE, IS UNKNOWN — единственные, которые всегда возвращают определённый результат (t или f); именно ими «выходят» из трёхзначной логики. Проверка на равенство NULL обязана записываться как IS NULL: выражение x = NULL никогда не истинно ни для какого x, даже когда x сам NULL. Заметим, что законы двузначной логики, например закон исключённого третьего A OR NOT A, в трёхзначной не действуют: при неизвестном A это выражение неизвестно.

Как неизвестное превращается в ложь и обратно

Ограничения, запросы и операции над множествами по-разному обращаются с UNKNOWN, и эти расхождения стоит собрать в одном месте (рисунок ниже).

• Предложения WHERE, HAVING и условие соединения ON оставляют строку, только если условие истинно. UNKNOWN приравнивается к лжи.
• Ограничение CHECK отвергает строку, только если условие ложно. UNKNOWN приравнивается к истине, поэтому строка с NULL проходит проверку (пример x > 0 выше).
• DISTINCT, GROUP BY, UNION, EXCEPT и INTERSECT считают два NULL неразличимыми (англ. not distinct), хотя NULL = NULL не истинно.
• Обычный UNIQUE считает NULL различными и допускает любое их число; UNIQUE NULLS NOT DISTINCT — одинаковыми.

Из первого пункта вытекает неприятное следствие, которое было показано в теме 3: условие и его отрицание вместе не покрывают таблицу.

SELECT count(*) FILTER (WHERE city = 'Казань')        AS eq,
       count(*) FILTER (WHERE NOT (city = 'Казань'))  AS neq,
       count(*) FILTER (WHERE city IS NULL)           AS nulls,
       count(*)                                       AS total
FROM customer;

 eq  | neq  | nulls | total 
-----+------+-------+-------
 562 | 3967 | 471   |  5000
(1 row)

562 + 3967 = 4529, а покупателей 5000: 471 покупатель с неизвестным городом не вошёл ни в «живущих в Казани», ни в «живущих не в Казани». Для вопросов, подразумевающих «все, кроме», нужны инструменты, которые сравнивают с учётом NULL. Предикат IS DISTINCT FROM (и его отрицание IS NOT DISTINCT FROM) — сравнение, никогда не возвращающее UNKNOWN: два NULL считаются равными, а NULL и определённое значение — различными ¹¹. Функция coalesce(a, b, …) возвращает первый аргумент, не равный NULL, и позволяет подставить значение вместо неопределённого, а nullif(a, b) — наоборот, возвращает NULL, если аргументы равны. Типичное применение второй — защита от деления на ноль:

SELECT NULL = NULL                  AS eq,
       NULL IS NOT DISTINCT FROM NULL AS not_distinct,
       1 IS DISTINCT FROM NULL      AS distinct_from,
       coalesce(NULL, 'x')          AS coal,
       nullif(5, 5)                 AS n1,
       nullif(5, 6)                 AS n2;

SELECT 10 / nullif(0, 0) AS safe;

  eq  | not_distinct | distinct_from | coal |  n1  | n2 
------+--------------+---------------+------+------+----
 NULL | t            | t             | x    | NULL |  5
(1 row)

 safe 
------
 NULL
(1 row)

Для пропуска NULL в вопросах «все, кроме Казани» запрос пишут как city IS DISTINCT FROM 'Казань' (получим 4438 строк = 5000 − 562, включая 471 с неизвестным городом) или city <> 'Казань' OR city IS NULL. Какой вариант верен, зависит от смысла: если неизвестный город не должен попасть в ответ, достаточно обычного <>. Это решение нужно принимать осознанно, каждый раз, когда в условии участвует столбец, допускающий NULL. Сравнения строк (кортежей) работают по тем же правилам: ROW(1, NULL) = ROW(1, NULL) даёт NULL, а ROW(1, NULL) IS NOT DISTINCT FROM ROW(1, NULL) — истину ¹¹. Поведение агрегатных функций с NULL (count(*) против count(city) и т. п.) — тема 5.

Ловушка NOT IN

Самая коварная форма трёхзначной логики — предикат NOT IN над подзапросом, в результате которого есть NULL. Раскроем определение. Выражение x IN (a, b, c) означает x = a OR x = b OR x = c; значит, x NOT IN (a, b, c) равносильно x <> a AND x <> b AND x <> c. Если одно из значений списка — NULL, то соответствующий конъюнкт x <> NULL неизвестен, а конъюнкция со значением UNKNOWN не может быть истинной: либо ложь (когда x совпал с каким-то определённым значением), либо неизвестно. Результат: ни для какого x выражение не станет истинным. Убедимся на литералах:

SELECT 1 IN (1, NULL)     AS a,
       2 IN (1, NULL)     AS b,
       2 NOT IN (1, NULL) AS c,
       2 NOT IN (1, 3)    AS d;

 a |  b   |  c   | d 
---+------+------+---
 t | NULL | NULL | t
(1 row)

Теперь на данных учебной базы. Вопрос: у скольких покупателей город не совпадает ни с одним городом издательств? Города издательств хранятся в publisher.city, и у «Северного узла» он не указан (NULL). Вот три формулировки:

-- 1. NOT IN: в подзапросе есть NULL
SELECT count(*) FROM customer
WHERE city NOT IN (SELECT city FROM publisher);

-- 2. NOT EXISTS
SELECT count(*) FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM publisher p WHERE p.city = c.city);

-- 3. NOT IN с отбрасыванием NULL в подзапросе
SELECT count(*) FROM customer
WHERE city NOT IN (SELECT city FROM publisher WHERE city IS NOT NULL);

 count 
-------
     0

 count 
-------
  3313

 count 
-------
  2842

Три результата для «одного и того же» вопроса. Первый запрос возвращает 0: список содержит NULL, и NOT IN не истинно ни для одной строки. Второй возвращает 3313 — покупателей, чьего города нет среди городов издательств, включая 471 покупателя без города, потому что NOT EXISTS проверяет только существование совпавшей строки, а для NULL оно не найдено. Третий возвращает 2842 = 3313 − 471: теперь список без NULL, но сам левый операнд city у 471 покупателей неизвестен, NULL NOT IN (…) неизвестно, и они в ответ не попадают. Какой результат «правильный», решает смысл вопроса: хотим ли мы покупателей с неизвестным городом считать «не живущими в городах издательств»? Но результат 0 неверен при любом смысле: это не ответ, а следствие NULL, которого запрос не заметил.

Ещё один пример на другой паре таблиц: у 95 книг издательство не указано. Какие издательства не выпустили ни одной книги дороже 3000 рублей?

SELECT count(*) FROM publisher
WHERE publisher_id NOT IN (SELECT publisher_id FROM book WHERE price >= 3000);

SELECT count(*) FROM publisher p
WHERE NOT EXISTS (SELECT 1 FROM book b WHERE b.publisher_id = p.publisher_id AND b.price >= 3000);

 count 
-------
     0

 count 
-------
    10

Среди книг дороже 3000 одна (с ценой 3990) не имеет издательства, поэтому подзапрос NOT IN содержит NULL и ответ получился пустым, а на самом деле такие издательства — все десять. Заметим, как зловещ механизм: запрос не падает с ошибкой, он молча возвращает неверный результат, и с ростом данных NULL в подзапросе может появиться неожиданно. Сегодня запрос работает, завтра кто-то вставит строку без издательства, и отчёт опустеет.

Практические правила:

• для вопросов «нет такой строки в другой таблице» использовать NOT EXISTS (антисоединение; подробно — в теме 4);
• NOT IN допустим для списка литералов без NULL и для подзапроса по столбцу с NOT NULL, но надёжность последнего держится на ограничении схемы, то есть опять на целостности данных;
• в подзапросе под NOT IN NULL отбрасывают явно: WHERE col IS NOT NULL;
• проверка NULL-осведомлённости каждого условия — часть вычитки запроса: для каждого столбца условия задаём вопрос «может ли здесь быть NULL?».

Эти правила не специфичны для PostgreSQL: так определён NOT IN в стандарте, и ловушка воспроизводится во всех СУБД ⁷¹¹. Планировщик PostgreSQL по тем же причинам не преобразует NOT IN с подзапросом в эффективное антисоединение, как NOT EXISTS, — тема 11.

Литература

1. Codd E. F. A Relational Model of Data for Large Shared Data Banks. — Communications of the ACM, 1970, Т. 13, № 6, С. 377–387, DOI: 10.1145/362384.362685.
2. Codd E. F. Is Your DBMS Really Relational? / Does Your DBMS Run by the Rules? — Computerworld, 1985, Т. 19, № 41, С. 1–16; № 42, С. 49–60.
3. Codd E. F. The Relational Model for Database Management: Version 2. — Addison-Wesley, 1990.
4. Дейт К. Дж. Введение в системы баз данных. 8-е изд. — М.: Вильямс, 2005. (Date C. J. An Introduction to Database Systems. 8th ed. — Addison-Wesley, 2004.)
5. Date C. J., Darwen H. Foundation for Future Database Systems: The Third Manifesto. 2nd ed. — Addison-Wesley, 2000.
6. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.
7. ISO/IEC 9075-2:2023. Information technology — Database languages SQL — Part 2: Foundation (SQL/Foundation). — ISO, 2023.
8. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. https://postgrespro.ru/education/books/internals
9. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Chapter 5. Data Definition — Constraints. — 2023. https://www.postgresql.org/docs/16/ddl-constraints.html
10. Chamberlin D. D., Boyce R. F. SEQUEL: A Structured English Query Language. — Proceedings of the 1974 ACM SIGFIDET Workshop, 1974, С. 249–264.
11. Melton J., Simon A. R. SQL: 1999: Understanding Relational Language Components. — Morgan Kaufmann, 2001.