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

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

Охранник на входе

На входе в бизнес-центр стоит охранник со списком правил: пропуск обязателен, по одному пропуску проходит один человек, детей до 14 лет без взрослых не пускать. Охраннику всё равно, кто тебя прислал и как ты торопишься. Нарушил правило, значит не прошёл.

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

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

Четыре вида

Таблица книг с полным набором охраны:

CREATE TABLE books (
  id    integer       PRIMARY KEY,
  isbn  text          NOT NULL UNIQUE,
  title text          NOT NULL,
  price numeric(8, 2) CHECK (price > 0),
  pages integer       NOT NULL CHECK (pages > 0)
);

INSERT INTO books VALUES (1, '978-5-389-01686-6', 'Мастер и Маргарита', 690.00, 480);

Ограничение	Правило	Где в таблице
NOT NULL	значение обязательно	title, isbn, pages
UNIQUE	значение не повторяется	isbn
PRIMARY KEY	не повторяется и не пустое	id
CHECK	выражение истинно	price, pages

NOT NULL мы уже знаем. Нарушим по очереди остальные три.

PRIMARY KEY

Первичный ключ это номер строки, по которому её всегда можно найти. Вставим вторую книгу с тем же id:

INSERT INTO books VALUES (1, '978-5-17-090334-4', 'Пикник на обочине', 450.00, 224);

ERROR:  duplicate key value violates unique constraint "books_pkey"
DETAIL:  Key (id)=(1) already exists.

Первичный ключ это UNIQUE и NOT NULL вместе. Ключ у таблицы может быть только один, и его стоит заводить в каждой таблице, даже если кажется, что он не нужен.

UNIQUE

UNIQUE запрещает повторы, но первичным ключом не считается. Подходит для альтернативных ключей: у книги есть id, но и ISBN двух книг совпадать не может.

INSERT INTO books VALUES (2, '978-5-389-01686-6', 'Пикник на обочине', 450.00, 224);

ERROR:  duplicate key value violates unique constraint "books_isbn_key"
DETAIL:  Key (isbn)=(978-5-389-01686-6) already exists.

Сообщение почти такое же, как у ключа: под капотом оба работают через уникальный индекс.

CHECK

CHECK принимает любое логическое выражение. Отрицательная цена:

INSERT INTO books VALUES (2, '978-5-17-090334-4', 'Пикник на обочине', -450.00, 224);

ERROR:  new row for relation "books" violates check constraint "books_price_check"
DETAIL:  Failing row contains (2, 978-5-17-090334-4, Пикник на обочине, -450.00, 224).

А теперь без цены вовсе:

INSERT INTO books VALUES (2, '978-5-17-090334-4', 'Пикник на обочине', NULL, 224);
SELECT id, title, price FROM books ORDER BY id;

 id |       title        | price
----+--------------------+--------
  1 | Мастер и Маргарита | 690.00
  2 | Пикник на обочине  |
(2 rows)

Строка прошла. Это не ошибка: NULL > 0 даёт не ложь, а «неизвестно», трёхзначная логика в действии. CHECK отклоняет только явную ложь. Если цена обязательна, нужен ещё и NOT NULL, как у pages.