CREATE TABLE: схема, ограничения и ключи

CREATE TABLE — это не просто объявление структуры таблицы, это спецификация инвариантов данных, которые PostgreSQL будет проверять при каждой операции записи, независимо от того, кто пишет — Django-приложение, скрипт миграции, SQL-консоль или другой микросервис. Ограничения (constraints) бывают пяти видов: NOT NULL (обязательность), UNIQUE (уникальность), PRIMARY KEY (идентификатор строки), FOREIGN KEY (ссылочная целостность), CHECK (произвольное бизнес-правило). Каждое из них решает конкретную проблему с данными и работает как барьер, не требующий кода в приложении. Правильно спроектированная схема с ограничениями — самодокументирующаяся: читая CREATE TABLE, любой разработчик сразу видит все инварианты системы.

Почему это важно: Представь ситуацию: три года назад кто-то решил 'добавим валидацию в ORM, зачем дублировать в БД'. Теперь в production-базе: 15% пользователей с NULL email, заказы с несуществующими user_id (удалённый пользователь), товары с отрицательной ценой, статусы 'PENDIGN', 'pendng', 'Pending' — результат разных версий кода, скриптов импорта и прямых SQL-операций поддержки. Миграция этих данных, поиск и исправление всех ошибок займёт месяцы. Ограничения в БД решают это раз и навсегда: ORM обходят скриптом — CHECK сработает. Микросервис вставляет без валидации — FOREIGN KEY не пропустит. Прямой SQL в панике поддержки — NOT NULL остановит. Ограничения на уровне базы данных — последняя линия обороны, которую невозможно обойти.

Главная идея

Каждое ограничение в PostgreSQL реализовано как часть движка, которая проверяется синхронно при каждой DML-операции (INSERT, UPDATE, DELETE). Если ограничение нарушено — операция откатывается с ошибкой, данные не записываются. PRIMARY KEY физически создаёт уникальный B-tree индекс на столбце — поэтому поиск по primary key всегда использует индекс. UNIQUE также создаёт индекс. FOREIGN KEY при вставке делает lookup в родительскую таблицу (нужен индекс на referenced столбце). CHECK вычисляет булево выражение — важно помнить, что NULL в CHECK даёт unknown, а не false, поэтому NULL проходит CHECK (price > 0). NOT NULL — самое быстрое ограничение: хранится как флаг в системных таблицах, не требует дополнительных вычислений.

Как это выглядит на практике

Начнём с проектирования схемы для e-commerce: таблицы users, products, orders, order_items. Это классический пример, который показывает все виды ограничений.
Таблица users: CREATE TABLE users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now()). PRIMARY KEY на id — это уникальный идентификатор. UNIQUE на email — нельзя зарегистрировать два аккаунта с одним email. NOT NULL на email и full_name — обязательные поля.
Таблица products: добавляем CHECK для бизнес-правил: CREATE TABLE products (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0), sku TEXT NOT NULL UNIQUE). CHECK (price >= 0) гарантирует, что цена не может быть отрицательной даже при прямом SQL.
Таблица orders с FOREIGN KEY: CREATE TABLE orders (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')), total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0), created_at TIMESTAMPTZ NOT NULL DEFAULT now()). REFERENCES users(id) — ссылочная целостность. ON DELETE RESTRICT — нельзя удалить пользователя с заказами.
ON DELETE: три варианта поведения при удалении родительской записи. RESTRICT (default) — запрещает удаление если есть дочерние записи. CASCADE — каскадно удаляет все дочерние записи. SET NULL — устанавливает foreign key в NULL (столбец должен допускать NULL). Выбор зависит от бизнес-логики: для заказов правильно RESTRICT, для сессий пользователя — CASCADE.
Таблица order_items — составной первичный ключ: CREATE TABLE order_items (order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT, quantity INTEGER NOT NULL CHECK (quantity > 0), price_at_purchase NUMERIC(10,2) NOT NULL CHECK (price_at_purchase >= 0), PRIMARY KEY (order_id, product_id)). Составной PK из (order_id, product_id) гарантирует, что один товар не может быть в одном заказе дважды.
Именованные ограничения: CONSTRAINT chk_price CHECK (price >= 0). Именование помогает в диагностике — ошибка будет 'violates check constraint chk_price', а не 'violates check constraint orders_price_check'. Именуй ограничения для критичных бизнес-правил.
Ограничение на уровне таблицы vs столбца: CHECK (start_date < end_date) — ограничение на уровне таблицы, так как сравнивает два столбца. CONSTRAINT chk_dates CHECK (start_date < end_date) — пишется после всех столбцов перед закрывающей скобкой.
DEFERRABLE ограничения: FOREIGN KEY REFERENCES categories(id) DEFERRABLE INITIALLY DEFERRED — ограничение проверяется не при каждой операции, а в конце транзакции. Нужно при circular references: categories ссылается на parent_category_id из той же таблицы — при вставке дочерней категории раньше родителя обычный FK выдаст ошибку, DEFERRED ждёт завершения транзакции.
Добавление ограничений к существующей таблице: ALTER TABLE products ADD CONSTRAINT chk_positive_price CHECK (price > 0). Для больших таблиц это блокирующая операция — PostgreSQL проверяет каждую строку. Для добавления UNIQUE без блокировки: сначала CREATE UNIQUE INDEX CONCURRENTLY, потом ALTER TABLE ADD CONSTRAINT USING INDEX.
Partial UNIQUE: CREATE UNIQUE INDEX ON users(email) WHERE deleted_at IS NULL — уникальность email только среди активных пользователей. Это позволяет 'удалять' пользователей через soft delete, сохраняя возможность перерегистрации с тем же email.
Просмотр ограничений таблицы: SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'orders'::regclass. contype: 'p' = PRIMARY KEY, 'f' = FOREIGN KEY, 'u' = UNIQUE, 'c' = CHECK, 'n' = NOT NULL.

Примеры кода

Полная схема e-commerce с всеми типами ограничений

CREATE TABLE users (
  id            BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         TEXT         NOT NULL,
  full_name     TEXT         NOT NULL,
  is_active     BOOLEAN      NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  CONSTRAINT uq_users_email UNIQUE (email)
);

CREATE TABLE products (
  id             BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name           TEXT            NOT NULL,
  sku            TEXT            NOT NULL,
  price          NUMERIC(10, 2)  NOT NULL,
  stock_quantity INTEGER         NOT NULL DEFAULT 0,
  category       TEXT            NOT NULL,
  created_at     TIMESTAMPTZ     NOT NULL DEFAULT now(),
  CONSTRAINT uq_products_sku UNIQUE (sku),
  CONSTRAINT chk_price_positive CHECK (price >= 0),
  CONSTRAINT chk_stock_nonneg  CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
  id           BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id      BIGINT          NOT NULL,
  status       TEXT            NOT NULL DEFAULT 'pending',
  total_amount NUMERIC(12, 2)  NOT NULL DEFAULT 0,
  notes        TEXT,  -- может быть NULL
  created_at   TIMESTAMPTZ     NOT NULL DEFAULT now(),
  CONSTRAINT fk_orders_user     FOREIGN KEY (user_id)
                                REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT chk_order_status   CHECK (status IN (
                                  'pending','confirmed','shipped',
                                  'delivered','cancelled')),
  CONSTRAINT chk_order_total    CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
  order_id           BIGINT         NOT NULL,
  product_id         BIGINT         NOT NULL,
  quantity           INTEGER        NOT NULL,
  price_at_purchase  NUMERIC(10,2)  NOT NULL,
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)
                              REFERENCES orders(id)  ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id)
                              REFERENCES products(id) ON DELETE RESTRICT,
  CONSTRAINT chk_qty_positive CHECK (quantity > 0),
  CONSTRAINT chk_price_snap   CHECK (price_at_purchase >= 0)
);

Именованные ограничения (CONSTRAINT name ...) дают понятные ошибки при нарушении. ON DELETE CASCADE на order_items — удаление заказа каскадно удаляет его позиции. ON DELETE RESTRICT на orders — нельзя удалить пользователя пока у него есть заказы.

Что происходит при нарушении ограничений

-- NOT NULL нарушение
INSERT INTO users (full_name) VALUES ('Alice');
-- ERROR: null value in column "email" of relation "users"
--        violates not-null constraint

-- UNIQUE нарушение
INSERT INTO users (email, full_name) VALUES ('alice@example.com', 'Alice');
INSERT INTO users (email, full_name) VALUES ('alice@example.com', 'Alice 2');
-- ERROR: duplicate key value violates unique constraint "uq_users_email"
--        DETAIL: Key (email)=(alice@example.com) already exists.

-- CHECK нарушение
INSERT INTO products (name, sku, price, category)
VALUES ('Widget', 'SKU-001', -100, 'gadgets');
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"
--        DETAIL: Failing row contains (1, Widget, SKU-001, -100.00, 0, gadgets, ...)

-- FOREIGN KEY нарушение
INSERT INTO orders (user_id, status) VALUES (99999, 'pending');
-- ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_user"
--        DETAIL: Key (user_id)=(99999) is not present in table "users".

PostgreSQL даёт подробные сообщения об ошибках с именем ограничения и деталями нарушения. Именованные ограничения (CONSTRAINT name ...) делают ошибки читаемыми — разработчик сразу понимает что нарушено.

Zero-downtime добавление UNIQUE индекса

-- Проблема: обычный ADD UNIQUE блокирует таблицу
-- ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE(email);
-- Это держит ACCESS EXCLUSIVE LOCK пока проверяет все строки!

-- Решение: 2-шаговый процесс без блокировки

-- Шаг 1: создаём индекс конкурентно (не блокирует чтение/запись)
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Шаг 2: привязываем индекс как ограничение
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE USING INDEX idx_users_email;

-- Проверяем ограничения таблицы
SELECT
  conname AS constraint_name,
  contype AS type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass
ORDER BY contype;

CREATE UNIQUE INDEX CONCURRENTLY строит индекс без блокировки таблицы — другие запросы продолжают работать. Занимает больше времени, но безопасно для продакшена. ALTER TABLE ADD CONSTRAINT USING INDEX конвертирует индекс в named constraint.

Что происходит под капотом

PRIMARY KEY физически создаёт уникальный B-tree индекс в PostgreSQL. Этот индекс используется как для уникальности, так и для быстрого поиска по pk. Размер индекса — дополнительные 8 байт per row для BIGINT-ключа плюс overhead B-tree структуры. Для таблицы из 100 миллионов строк индекс первичного ключа занимает ~2-3GB.
FOREIGN KEY при каждом INSERT и UPDATE в дочернюю таблицу выполняет SELECT 1 FROM parent WHERE pk = value. Для этого нужен индекс на referenced столбце в родительской таблице (он всегда есть — это primary key). Без индекса на foreign key столбце в дочерней таблице — каждая операция в родительской таблице (DELETE, UPDATE pk) вызывает seq scan дочерней. Это частая причина deadlock и медленных DELETE.
CHECK ограничение вычисляется для каждой строки при INSERT и UPDATE. Если выражение возвращает true — ограничение выполнено. Если false — ошибка. Если NULL — ограничение считается выполненным (NULL = unknown). Поэтому CHECK (price > 0) пропустит NULL price. Для запрета NULL нужно отдельное NOT NULL.
NOT NULL реализован как флаг в системном каталоге pg_attribute.attnotnull — без дополнительных данных, не требует индекса. Это самая дешёвая проверка. NOT NULL также влияет на планировщик: он знает, что столбец никогда не NULL, и может избегать IS NOT NULL фильтров в планах.
UNIQUE создаёт уникальный B-tree индекс аналогично PRIMARY KEY. Разница: PRIMARY KEY не допускает NULL, UNIQUE допускает несколько NULL (NULL не равен NULL, поэтому несколько NULL не нарушают уникальность). Это важно для soft delete паттерна: UNIQUE (email) позволяет удалить аккаунт и создать новый с тем же email.
ALTER TABLE ADD CONSTRAINT CHECK/NOT NULL — блокирующая операция для существующих данных. PostgreSQL удерживает ACCESS EXCLUSIVE LOCK пока проверяет каждую строку. Для таблиц > 10GB это может занять минуты и заблокировать всё чтение. Безопасная альтернатива: сначала ALTER TABLE ADD CONSTRAINT ... NOT VALID (добавляет ограничение без проверки существующих строк), потом ALTER TABLE VALIDATE CONSTRAINT ... (проверяет с меньшим ShareUpdateExclusiveLock).
DEFERRABLE ограничения: обычные ограничения проверяются IMMEDIATELY (сразу после каждой операции). DEFERRABLE INITIALLY DEFERRED переносит проверку на COMMIT транзакции. Это нужно для circular references: menu_items.parent_id REFERENCES menu_items(id). При загрузке дерева меню можно вставить все узлы и проверить целостность только в конце транзакции.
Ограничения исключения (EXCLUDE): CREATE TABLE reservations (..., EXCLUDE USING GIST (room_id WITH =, during WITH &&)). Это обобщение UNIQUE — запрещает перекрытие диапазонов времени для одной комнаты. Требует extension btree_gist. Используется для бронирования ресурсов, расписаний, интервалов.
Partial unique index: CREATE UNIQUE INDEX ON users(email) WHERE is_active = true. Обычный UNIQUE INDEX невозможно создать если нужна уникальность только среди части строк — partial index решает это. Нет прямого синтаксиса для partial unique constraint в DDL, только через CREATE UNIQUE INDEX + ALTER TABLE ADD CONSTRAINT USING INDEX.
Каждое ограничение хранится в pg_constraint с полями conname (имя), contype (тип), conrelid (oid таблицы), conkey (номера столбцов), confrelid (oid referenced таблицы для FK). pg_get_constraintdef(oid) возвращает SQL-определение ограничения — удобно для документирования схемы.

Типичные ошибки и заблуждения

«Ограничения в ORM (validates :email, presence: true) достаточны, дублировать в БД избыточно». Это самое опасное заблуждение. ORM-валидация работает только через этот ORM. Прямые SQL-запросы её обходят. Скрипты импорта данных — обходят. Другие микросервисы — обходят. Джуниор, пишущий UPDATE напрямую в psql — обходит. Ограничения в БД работают для всех без исключения. Принцип: БД — последний рубеж. ORM-валидация — удобный слой для UX (даёт пользователю понятные ошибки до SQL-запроса), но не замена БД-ограничениям.
«ON DELETE CASCADE — правильный выбор для всех foreign keys». CASCADE удаляет дочерние записи без предупреждения и без возможности восстановления. Удаляешь пользователя — автоматически удаляются все его заказы, отзывы, настройки. В большинстве случаев правильнее RESTRICT (бизнес должен явно решить что делать с дочерними данными) или SET NULL (обнулить ссылку, но сохранить запись). CASCADE разумен для технических данных: сессии, токены, логи активности.
«NOT NULL можно добавить к существующей таблице без downtime». ALTER TABLE orders ALTER COLUMN status SET NOT NULL — для таблицы с 50 миллионами строк это займёт минуты и заблокирует таблицу. Правильный путь: сначала заполни все NULL-строки значением, затем добавь ограничение. Ещё лучше: ALTER TABLE ADD CONSTRAINT chk_not_null CHECK (status IS NOT NULL) NOT VALID, потом ALTER TABLE VALIDATE CONSTRAINT — валидация с меньшим lock.
«Составной PRIMARY KEY (order_id, product_id) медленнее простого integer id». Это неверно. Составной PK создаёт составной индекс, который так же эффективен для lookup по полному ключу. Плюс: он автоматически гарантирует, что один product не может быть в одном order дважды — это бизнес-правило, закодированное без дополнительного UNIQUE.
«NULL в UNIQUE столбце не допустим». В PostgreSQL несколько NULL в UNIQUE-столбце разрешены — NULL не равен NULL. Это позволяет иметь несколько строк без значения в уникальном столбце (например, несколько пользователей без email в столбце email UNIQUE). Это отличие от SQL Server и старых версий MySQL, где UNIQUE допускает только один NULL.
«CHECK ограничение работает как NOT NULL для NULL значений». Нет. CHECK (price > 0) пропустит NULL price: NULL > 0 = NULL (unknown), и ограничение считается выполненным. Если нужно запретить и NULL, и отрицательные значения — добавляй оба: price NUMERIC(10,2) NOT NULL CHECK (price >= 0).
«FOREIGN KEY автоматически создаёт индекс на столбце». PostgreSQL создаёт индекс на referenced столбце в родительской таблице (это primary key). На FK-столбце в дочерней таблице индекс НЕ создаётся автоматически! Его нужно создавать вручную: CREATE INDEX ON orders(user_id). Без него: каждый DELETE в users вызывает seq scan всей orders — медленно и при большой нагрузке вызывает deadlocks.

Ключевые выводы

Ограничения в БД работают для всех клиентов без исключения — ORM, прямой SQL, скрипты, другие сервисы. Это последняя линия защиты данных.
PRIMARY KEY = NOT NULL + UNIQUE + автоматический B-tree индекс. Всегда используй BIGINT GENERATED ALWAYS AS IDENTITY.
FOREIGN KEY обеспечивает ссылочную целостность, но требует ручного создания индекса на FK-столбце в дочерней таблице.
CHECK (expr) пропускает NULL — если нужно запретить NULL отдельно добавляй NOT NULL.
ON DELETE RESTRICT безопаснее CASCADE для бизнес-данных: заставляет явно решить что делать с дочерними записями.
Именуй ограничения (CONSTRAINT name): читаемые ошибки вместо 'violates constraint orders_status_check1'.
Добавление ограничений к большим таблицам без downtime: CREATE INDEX CONCURRENTLY + ALTER TABLE ADD CONSTRAINT USING INDEX.

Термины урока

NOT NULL: ограничение, запрещающее NULL в столбце; реализовано как флаг в pg_attribute, без накладных расходов на индекс.

UNIQUE: ограничение уникальности значений; физически создаёт уникальный B-tree индекс; допускает несколько NULL в PostgreSQL.

PRIMARY KEY: уникальный идентификатор строки; NOT NULL + UNIQUE; создаёт B-tree индекс; у каждой таблицы может быть только один.

FOREIGN KEY: ссылочное ограничение, проверяющее существование referenced записи в родительской таблице при каждом INSERT/UPDATE.

CHECK: ограничение с произвольным булевым выражением; NULL в выражении = unknown = ограничение выполнено.

ON DELETE CASCADE / RESTRICT / SET NULL: поведение при удалении родительской записи; CASCADE удаляет дочерние, RESTRICT запрещает удаление, SET NULL обнуляет FK.

DEFERRABLE / DEFERRED: ограничение, проверяемое не после каждой операции, а в момент COMMIT транзакции; для circular references.

NOT VALID: опция ALTER TABLE ADD CONSTRAINT, добавляющая ограничение без проверки существующих строк; нужно потом VALIDATE CONSTRAINT.

Partial unique index: уникальный индекс с WHERE-условием; позволяет уникальность только среди подмножества строк.

pg_constraint: системная таблица с метаданными обо всех ограничениях; pg_get_constraintdef(oid) возвращает SQL-определение.

Связь с работой backend-разработчика

Философия проектирования схемы: каждое бизнес-правило, которое 'всегда должно быть истинным', кодируй в БД как ограничение. Email пользователя всегда уникален — UNIQUE. Цена не может быть отрицательной — CHECK. Заказ всегда привязан к существующему пользователю — FOREIGN KEY. Это стоит нескольких дополнительных строк в CREATE TABLE, но экономит часы отладки и дни 'расчистки данных' в будущем. Практическое правило для добавления ограничений к production-таблицам: если таблица < 10 миллионов строк, обычный ALTER TABLE ADD CONSTRAINT займёт секунды. Для больших таблиц используй NOT VALID + VALIDATE CONSTRAINT для минимизации блокировок. Для UNIQUE — обязательно CREATE UNIQUE INDEX CONCURRENTLY перед привязкой constraint.

Мини-разбор реальной ситуации

Команда строит маркетплейс. На старте решают 'ограничения в БД — это для корпоратов, мы используем ORM-валидацию'. Через 18 месяцев: кампания A/B-тестирования запустила скрипт обновления цен напрямую через SQL-запрос, обходя ORM — 2000 товаров получили цену -1 (ошибка в скрипте). Служба поддержки импортировала CSV с заказами старой системы — 340 заказов ссылаются на несуществующих пользователей (user_id = NULL или несуществующие ID). Мобильное приложение в версии 2.3 отправляло статус 'PAID' вместо 'paid' — 15 000 заказов с невалидным статусом. Итог: 3 недели работы по очистке данных, написание скриптов проверки и миграций. С ограничениями в схеме: CHECK (price >= 0), FOREIGN KEY (user_id) REFERENCES users(id), CHECK (status IN (...)) — каждая из этих ситуаций была бы поймана немедленно с понятной ошибкой.

Что запомнить

Ограничения в БД — не дублирование ORM-валидации; это защита от всех путей записи данных, не только через приложение.
Всегда создавай индекс на FK-столбце дочерней таблицы (CREATE INDEX ON orders(user_id)) — без него DELETE в родительской таблице вызывает seq scan.
CHECK (col > 0) не запрещает NULL — для запрета NULL нужно отдельное NOT NULL ограничение.
Именуй ограничения (CONSTRAINT chk_price CHECK ...) — читаемые ошибки делают debugging в 5 раз быстрее.
ON DELETE RESTRICT по умолчанию для бизнес-данных; CASCADE только для технических (сессии, логи).
Большие таблицы: NOT VALID + VALIDATE CONSTRAINT вместо прямого ADD CONSTRAINT для минимизации блокировок.

Итог

CREATE TABLE с ограничениями — это спецификация системы, написанная на языке базы данных. Хорошо спроектированная схема с ограничениями самодокументируется: любой разработчик, прочитав её, понимает все бизнес-инварианты. И что важнее: она самозащищается. Базу данных с правильными ограничениями почти невозможно 'испортить' случайным запросом.