Шесть ограничений SQL против мусора в базе: почему проверок в коде недостаточноЗаметка · дайджест за 22 ноября
Архитектурная изометрическая схема защитного шлюза базы данных с шестью ограничениями целостности схемы SQL
Заметка3 раздела≈ 4 мин

Шесть ограничений SQL против мусора в базе: почему проверок в коде недостаточно

На бэкенде всё чисто: запросы проверяются через Zod или Pydantic, тесты зелёные. Открываешь в понедельник прод-базу — а там два юзера с одним логином, оценка 150 при потолке в сотню и ссылка на удалённую команду. Сюрприз. Код при этом не выдал ни одной ошибки.

Зачем проверки в схеме, если валидация есть в бэкенде

«Погоди, зачем дублировать правила в SQL, если бэкенд и так всё отсеивает?» — резонный вопрос. Всё просто: твой сервис почти никогда не остаётся единственной дверью в базу.

Рядом быстро появляются воркер, скрипт миграции или второй микросервис, а в пятницу кто-то правит строки руками. Валидация в коде про эти входы не знает.

Даже один бэкенд ломается на гонке запросов. Два человека одновременно берут логин alex: код делает SELECT ... WHERE user_name = 'alex', оба видят пустоту и шлют INSERT. Привет, дубль.

Валидатор в приложении вежливо подсказывает, где пользователь ошибся. Ограничения в схеме (их задают через DDL — команды описания таблиц вроде CREATE TABLE и ALTER TABLE) служат последним рубежом для любого сервиса, скрипта и параллельного потока.

Шесть ограничений на одной схеме

Посмотрим все шесть правил из документации PostgreSQL по ограничениям на связке таблиц teams, users и workspaces:

-- Команды: первичный ключ и обязательное название
CREATE TABLE teams (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

-- Пользователи: уникальный логин и ссылка на команду
CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name TEXT NOT NULL,
  user_name TEXT NOT NULL UNIQUE,
  team_id INTEGER NOT NULL
    REFERENCES teams(id) ON DELETE RESTRICT
);

-- Рабочие пространства: оценка 0..100 и дата по умолчанию
CREATE TABLE workspaces (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id INTEGER NOT NULL
    REFERENCES teams(id) ON DELETE CASCADE,
  score NUMERIC(5, 2) NOT NULL CHECK (score BETWEEN 0 AND 100),
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

PRIMARY KEY даёт строке уникальный номер и запрещает NULL. NOT NULL не пускает пустоту в обязательные поля. UNIQUE держит уникальность логина по таблице и закрывает гонку запросов атомарно внутри СУБД.

FOREIGN KEY (через REFERENCES) хранит ссылочную целостность: в team_id нельзя записать несуществующую команду, так что «осиротевших» строк не будет. CHECK проверяет условие при каждой вставке и обновлении (score держится в 0..100). DEFAULT подставляет значение сам, если поле пропустили в INSERT (в PostgreSQL это формально свойство столбца, а не constraint, но держат его в том же наборе).

Попытка протащить кривые данные сразу получает отказ от СУБД:

-- Оценка 150 выходит за рамки CHECK (0..100)
INSERT INTO workspaces (team_id, score) VALUES (1, 150);
-- ERROR: new row violates check constraint "workspaces_score_check"

-- Команды с id = 999 нет в таблице teams
INSERT INTO users (display_name, user_name, team_id)
VALUES ('Аня', 'anya_dev', 999);
-- ERROR: insert or update violates foreign key "users_team_id_fkey"

Ошибка всплывает прямо на записи, а не через месяц в сломанном отчёте или упавшем ночном джобе.

Грабли с NULL, каскадами и большими таблицами

Первая засада — NULL внутри CHECK. Вешаешь CHECK (score BETWEEN 0 AND 100) без NOT NULL, и в таблицу спокойно залетает score = NULL. Сравнение NULL с числом в SQL даёт UNKNOWN, а не FALSE, и CHECK его пропускает. Нужен запрет пустоты — ставь NOT NULL рядом.

Вторая ловушка живёт в UNIQUE. По стандарту два NULL не равны друг другу, поэтому обычный UNIQUE пропустит сколько угодно пустых строк. Если нужен максимум один NULL, в PostgreSQL 15+ добавляют NULLS NOT DISTINCT.

Третье минное поле — ON DELETE у внешнего ключа. ON DELETE RESTRICT на users не даст удалить команду с людьми. А ON DELETE CASCADE (каскадное удаление) на workspaces молча снесёт все её пространства. Ставить CASCADE на автомате опасно: один DELETE способен вычистить полбазы.

И деталь про прод: обычный ALTER TABLE ... ADD CHECK на большой таблице блокирует запись на время полного сканирования. В PostgreSQL правило сначала вешают с NOT VALID, а старые строки проверяют вторым шагом:

-- Разрешаем максимум один NULL в уникальном поле (PostgreSQL 15+)
ALTER TABLE users
  ADD COLUMN external_id TEXT,
  ADD CONSTRAINT users_ext_uniq
    UNIQUE NULLS NOT DISTINCT (external_id);

-- Вешаем CHECK без долгой блокировки записи, затем проверяем старое
ALTER TABLE workspaces
  ADD CONSTRAINT ws_score_chk CHECK (score BETWEEN 0 AND 100) NOT VALID;
ALTER TABLE workspaces VALIDATE CONSTRAINT ws_score_chk;

После NOT VALID новые записи проверяются сразу, а VALIDATE CONSTRAINT спокойно прогоняет старые строки под мягкой блокировкой без остановки сервиса.

Хочу подробный разборГолоса помогают выбрать, что разобрать подробно. За этот материал 0 голосов.
Источники · 2