Foreign Key в PostgreSQL — связи таблиц, ALTER TABLE

Foreign Key (внешний ключ) связывает таблицы в PostgreSQL и гарантирует целостность данных. Разбираем как создать связи при создании таблицы и через ALTER TABLE, что делать при удалении и как писать JOIN.

Что такое внешний ключ и зачем он нужен

users (родительская таблица)       posts (дочерняя таблица)
┌────┬──────────────┐               ┌────┬───────────┬─────────┐
│ id │ email        │               │ id │ title     │ user_id │
├────┼──────────────┤               ├────┼───────────┼─────────┤
│  1 │ alice@b.com  │◄──────────────│  1 │ Первый    │    1    │
│  2 │ bob@b.com    │               │  2 │ Второй    │    1    │
└────┴──────────────┘               │  3 │ Третий    │    2    │
                                    └────┴───────────┴─────────┘

user_id в posts → REFERENCES users(id)
Нельзя вставить post с user_id = 99 если пользователя с id=99 нет

Без FK ничто не мешает создать запись с несуществующим user_id — получаете «висячие» ссылки. FK гарантирует ссылочную целостность на уровне базы данных, независимо от логики приложения.

Создание FOREIGN KEY при CREATE TABLE

-- Краткий синтаксис:
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id)
  -- NULL разрешён — пост без автора допустим
);

-- Строгий вариант с NOT NULL:
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

-- Именованный FK с действием при удалении:
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  CONSTRAINT posts_user_fk
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE    -- при удалении пользователя удалить его посты
    ON UPDATE CASCADE    -- при изменении id пользователя обновить user_id
);

Действия ON DELETE и ON UPDATE

-- CASCADE: посты удаляются вместе с пользователем
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

-- SET NULL: комментарии отвязываются от удалённого поста
CREATE TABLE comments (
  id      SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id) ON DELETE SET NULL,
  content TEXT NOT NULL
);

-- RESTRICT: нельзя удалить категорию если в ней есть товары
CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT
);

Добавить FOREIGN KEY через ALTER TABLE

-- Добавить новую колонку + FK одновременно:
ALTER TABLE posts
  ADD COLUMN category_id INTEGER,
  ADD CONSTRAINT posts_category_fk
    FOREIGN KEY (category_id)
    REFERENCES categories(id);

-- Добавить FK к существующей колонке:
ALTER TABLE posts
  ADD CONSTRAINT posts_user_fk
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE;

-- Удалить FK:
ALTER TABLE posts DROP CONSTRAINT posts_user_fk;

-- Посмотреть все FK таблицы:
SELECT conname AS constraint_name,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'posts'::regclass
  AND contype = 'f';

JOIN — соединение таблиц по внешнему ключу

-- INNER JOIN — только строки с совпадением в обеих таблицах:
SELECT u.email, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.active = TRUE
ORDER BY p.created_at DESC;

-- LEFT JOIN — все посты, даже без автора (user_id IS NULL):
SELECT p.title, u.email
FROM posts p
LEFT JOIN users u ON p.user_id = u.id;

-- Трёхтабличный JOIN:
SELECT u.email, p.title, c.content, c.created_at
FROM users u
JOIN posts p ON p.user_id = u.id
JOIN comments c ON c.post_id = p.id
WHERE u.active = TRUE
ORDER BY c.created_at DESC
LIMIT 20;

Изменение структуры таблицы

-- Добавить колонку:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Изменить тип колонки:
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Переименовать колонку:
ALTER TABLE users RENAME COLUMN username TO login;

-- Удалить колонку (и все зависимые индексы, ограничения):
ALTER TABLE users DROP COLUMN bio;

-- Переименовать таблицу:
ALTER TABLE users RENAME TO app_users;

-- Изменить схему таблицы:
ALTER TABLE users SET SCHEMA analytics;

Часто задаваемые вопросы

Как удалить внешний ключ в PostgreSQL?

Через ALTER TABLE: ALTER TABLE posts DROP CONSTRAINT posts_user_fk;. Для этого нужно знать имя constraint. Если создавали FK без имени — PostgreSQL присвоил автоматическое, посмотреть через \d posts в psql (раздел «Foreign-key constraints»). После удаления FK данные не удаляются — только снимается ограничение целостности. Можно удалить и создать заново с другими параметрами.

Почему нельзя удалить запись — ошибка внешнего ключа?

Ошибка ERROR: update or delete on table "users" violates foreign key constraint означает что на удаляемую строку ссылаются другие таблицы. По умолчанию (NO ACTION/RESTRICT) PostgreSQL запрещает удаление. Три варианта: 1) Сначала удалить зависимые записи вручную. 2) Добавить ON DELETE CASCADE к FK — дочерние записи удалятся автоматически. 3) Добавить ON DELETE SET NULL — FK колонка обнулится. Выбор зависит от бизнес-логики.

Что лучше: ON DELETE CASCADE или ON DELETE SET NULL?

Зависит от смысла связи. CASCADE — когда дочерние записи бессмысленны без родителя: удалил пользователя → его заказы и сессии тоже не нужны. SET NULL — когда дочерние записи сохраняют смысл: удалил автора → статьи остаются, но author_id = NULL. RESTRICT — когда нельзя удалить пока есть зависимые записи: категорию с товарами удалять нельзя. На практике CASCADE используют чаще всего для «личных» данных пользователя.