Ограничения и целостность: PRIMARY KEY, FOREIGN KEY

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

PRIMARY KEY: уникальный идентификатор

PRIMARY KEY - это основной ключ таблицы. Он:

Должен быть уникален (нет дубликатов)
Не может быть NULL
Идентифицирует строку однозначно

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY, -- автоинкрементирующееся число
  email TEXT NOT NULL,
  name TEXT NOT NULL
);

BIGSERIAL генерирует число автоматически (1, 2, 3, ...). Это удобнее, чем мануально вставлять ID.

Есть также простой SERIAL (32-bit) и SMALLSERIAL (16-bit), но для веб приложений юзай BIGSERIAL.

NOT NULL: запрет на пустоту

Без NOT NULL:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT, -- может быть NULL!
  price DECIMAL(10,2)
);

INSERT INTO products (id, name) VALUES (1, NULL); -- разрешено, но плохо

С NOT NULL:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL, -- NULL недопустим
  price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (name) VALUES (NULL); -- ошибка!

Правило: по умолчанию делай NOT NULL, а потом явно позволяй NULL если он действительно имеет смысл (например, отчество или номер телефона).

CHECK: свои правила

CHECK позволяет добавить пользовательские условия:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL,
  CONSTRAINT products_price_check CHECK (price > 0),
  CONSTRAINT products_quantity_check CHECK (quantity >= 0)
);

Попытка вставить отрицательную цену:

INSERT INTO products (name, price, quantity)
VALUES ('Widget', -50, 10); -- ошибка: CHECK failed

Более сложный пример:

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  status VARCHAR(20) NOT NULL,
  CONSTRAINT tasks_status_check CHECK (status IN ('new', 'active', 'done', 'archived'))
);

INSERT INTO tasks (title, status) VALUES ('Learn SQL', 'almost_done'); -- ошибка!
INSERT INTO tasks (title, status) VALUES ('Learn SQL', 'done'); -- ok

DEFAULT: умные значения по умолчанию

Вместо того, чтобы приложение подставляло значение, база может сделать это сама:

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  done BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Вставляем минимум данных
INSERT INTO tasks (user_id, title)
VALUES (1, 'Learn transactions');
-- status автоматически = 'new'
-- done автоматически = false
-- created_at автоматически = текущее время

Полезные DEFAULT:

DEFAULT now() - текущее время
DEFAULT false / DEFAULT true
DEFAULT 'some_value'
DEFAULT CURRENT_DATE

UNIQUE: уникальность для любого поля

PRIMARY KEY - это один уникальный индекс, но может быть несколько:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE, -- email уникален
  username TEXT NOT NULL UNIQUE, -- username тоже уникален
  phone TEXT
);

INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice');
INSERT INTO users (email, username) VALUES ('bob@example.com', 'bob');
INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice2'); -- ошибка: дубликат email

Или более явно:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Примечание: UNIQUE поле может быть NULL, и разных NULL может быть сколько угодно (NULL != NULL в SQL).

Под каждым UNIQUE база молча создаёт индекс - иначе проверять уникальность на миллионе строк было бы нечем.

FOREIGN KEY: связь между таблицами

FOREIGN KEY гарантирует, что значение в одной таблице ссылается на существующую строку в другой. По этим же ключам ты потом склеиваешь таблицы через JOIN.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  CONSTRAINT posts_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (email) VALUES ('alice@example.com'); -- id = 1
INSERT INTO posts (user_id, title) VALUES (1, 'Hello'); -- ok
INSERT INTO posts (user_id, title) VALUES (999, 'Hello'); -- ошибка: нет пользователя с id=999

ON DELETE: что происходит при удалении

Когда ты удаляешь пользователя, что делать с его постами?

ON DELETE: три стратегии - CASCADE удаляет связанные, SET NULL обнуляет, RESTRICT запрещает

ON DELETE CASCADE

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  CONSTRAINT posts_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

DELETE FROM users WHERE id = 1; -- удалит пользователя И все его посты!

Опасно! Одна команда может удалить половину базы. Именно поэтому в проде часто вместо реального удаления используют soft delete - смотри мини-проект трека.

ON DELETE SET NULL

CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT, -- может быть NULL
  post_id BIGINT NOT NULL,
  text TEXT NOT NULL,
  CONSTRAINT comments_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

DELETE FROM users WHERE id = 1; -- комментарии остаются, user_id = NULL («аноним»)

ON DELETE RESTRICT

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  total NUMERIC(10,2),
  CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

DELETE FROM users WHERE id = 1; -- ошибка: есть заказы от этого пользователя

Если `ON DELETE` не указан, PostgreSQL применяет `NO ACTION`. В обычном случае поведение то же - удаление запрещено, - но разница есть, и она практическая: `NO ACTION` допускает **отложенную** проверку.

CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
  DEFERRABLE INITIALLY DEFERRED

С таким ограничением внутри одной транзакции можно удалить пользователя, затем удалить или перевесить его заказы - проверка выполнится на COMMIT и увидит уже согласованное состояние. С RESTRICT этот приём не работает никогда: проверка происходит сразу и отложить её нельзя.

То есть RESTRICT строже дефолта, а не равен ему.

ON DELETE SET DEFAULT

-- Строка-заглушка обязана существовать ДО того, как на неё сошлётся FK.
INSERT INTO users (id, email, name)
VALUES (0, 'system@example.com', 'Система')
ON CONFLICT (id) DO NOTHING;

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL DEFAULT 0,
  title TEXT NOT NULL,
  CONSTRAINT posts_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
);

DELETE FROM users WHERE id = 1; -- посты перешли к user_id = 0 (система)

`SET DEFAULT` подставляет значение по умолчанию, **а потом проверяет внешний ключ**. Если строки с `id = 0` в `users` нет - а при `id BIGSERIAL` последовательность начинается с 1, значит её и не будет, - то `DELETE FROM users` падает:

ERROR: insert or update on table "posts" violates foreign key constraint
DETAIL: Key (user_id)=(0) is not present in table "users".

Причём падает не только этот DELETE, а любое удаление пользователя, у которого есть посты. Поэтому вставка заглушки - не украшение примера, а условие работоспособности схемы. И id у неё задаётся явно: BIGSERIAL сам ноль не выдаст.

Если заводить служебную строку не хочется, берите SET NULL (и тогда user_id должен быть nullable) или RESTRICT с явным переносом постов перед удалением.

Составной PRIMARY KEY

Иногда нужен PRIMARY KEY из нескольких колонок:

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

CREATE TABLE user_roles (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  assigned_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (user_id, role_id) -- пара (user_id, role_id) должна быть уникальна
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);
INSERT INTO user_roles (user_id, role_id) VALUES (1, 20); -- ok, другая роль
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10); -- ошибка: дубликат пары

Именование ограничений

Всегда давай понятные имена:

-- Плохо:
CONSTRAINT fk1 FOREIGN KEY (user_id) REFERENCES users(id)

-- Хорошо:
CONSTRAINT posts_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id)
CONSTRAINT products_price_check CHECK (price > 0)
CONSTRAINT users_email_unique UNIQUE (email)

Формат: {таблица}_{колонка}_{тип} (fk = foreign key, check = check, unique = unique).

Практические задания

Создай таблицу products с колонками: id (PK), name (NOT NULL), price (NOT NULL, CHECK > 0), quantity (NOT NULL, DEFAULT 0)
Добавь UNIQUE на name
Создай таблицу orders со связью на products (user_id, product_id FK)
Попробуй вставить заказ на несуществующий товар (должна быть ошибка)
Удали товар и посмотри поведение ON DELETE (сначала RESTRICT, потом CASCADE)
Создай составной PRIMARY KEY в таблице через две колонки