Ограничения и целостность: 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 (default)

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 SET DEFAULT

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 (система/админ)

Составной 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 в таблице через две колонки