Вам не нужны внешние ключи

Всем привет! В этой коротенькой статье я попытаюсь вам доказать, что, скорее всего, вам никогда не нужны внешние ключи (foreign keys) в СУБД.

TLDR

Гарантии внешних ключей (FK) не нужны при корректной архитектуре. При этом FK не бесплатны, БД постоянно проверяет, что родительские ключи существуют.

Что такое FK?

В ВУЗах на парах по базам данных студентам обязательно (наверно?) рассказывают про внешний ключ (далее также Foreign Key, FK). Рассказывают как-то так:

"Внешний ключ (foreign key) — это столбец или группа столбцов в одной таблице, значения которых ссылаются на первичный ключ другой таблицы. Это позволяет установить связь между двумя таблицами".

О цитате

Более корректное определение таково:

Внешний ключ (foreign key) — это столбец или группа столбцов в одной таблице (дочерней), который ссылается на первичный ключ или другую уникальную комбинацию столбцов в другой таблице (родительской). Это ключевой элемент реляционных баз данных для установления связи между таблицами и поддержания целостности данных, гарантируя, что значение в дочерней таблице существует в родительской

Я умышленно убрал часть определения, потому что большинство запоминают именно обрезанную версию :)

Что значит "установить связь", к сожалению, никто не понимает. В реальности же FK это гарантия валидности родительского ключа. Давайте разберем на примере. Скажем, мы разрабатываем интернет магазин с табличками user и order:

CREATE TABLE user(  
  id UUID PRIMARY KEY,  
  status TEXT NOT NULL,    
  
  balance BIGINT NOT NULL,  
  phone TEXT NOT NULL,  
  email TEXT NOT NULL  
  -- И другие поля
);

CREATE TABLE order(  
  id UUID PRIMARY KEY,  
  status TEXT NOT NULL,    
  
  user_id UUID NOT NULL,  
  amount BIGINT NOT NULL  
  -- И другие поля
);

Юный студент может захотеть для order добавить внешний ключ

FOREIGN KEY (user_id) REFERENCES user(id)

Что будет если добавить FK? Тогда при изменениях order и user будет проверяться, что order.user_id ссылается на существующий user.id. Иными словами FK гарантирует, что родительский ключ существует.

-- Операции, на которых FK может выдать ошибку
UPDATE user SET id = ?;
DELETE user;

INSERT INTO order; 
UPDATE order SET user_id = ?; 

Еще одна фича FK — on delete cascade - позволяет при удалении, например, user, сразу удалить все связанные order.
При этом делать join можно как с FK, так и без них. Внешние ключи в принципе никак не влияют на джойны и связи между таблицами

Так чем же мешают FK?

TLDR: они бесполезны.

По какой-то невиданной для меня причине, при изучении СУБД в университетах, всегда опускается клиент-серверная разработка, хотя она напрямую связана с проектированием СУБД. Из-за этого складывается ощущение, что FK — обязательная вещь в реальном проекте, хотя все наоборот — те гарантии, что дает FK, разработчик сам должен поддержать на стороне бэкенда. Иными словами — FK не дает дополнительных гарантий.

Давайте вернемся к нашему примеру с user и order и докажем, что при правильном проектировании бэкенда у нас не может быть нарушений консистентности.

Иммутабельность ID

Начнем с того, что у любой сущности ID должен быть иммутабельным. Не должно быть такого, что user поменял айдишник. (да и не понятно зачем и как это вообще возможно)

Таким образом иммутабельность айдишника гарантирует, что update на user никогда не сломает order.user_id.

UPDATE user SET id = ? -- невозможная операция 

Soft delete

FK могут помочь нам защититься от операций удаления из таблицы user, которые приведут к невалидным ссылкам в order. Но стоит ли вообще удалять данные из user?
Правильно, не нужно. Зачастую вместо delete используется подход, под названием soft delete. Например, вместо того, чтобы удалять юзера из БД, мы просто поменяем ему статус:

UPDATE user SET status = 'DELETED' WHERE id = ?

Это дает нам очень много преимуществ:

• Если кто-то случайно удалил не то — информацию мы не теряем, юзеров можно восстановить

• Проще отлаживать, потому что, опять же, мы не теряем информацию

• Это может быть требование бизнес логики. Например, мы не хотим терять информацию о том, когда юзер был создан, даже если он удалится.

Не могу утверждать, что этот подход является общеприменимым, но я пока что ни разу не встречал больших проектов, которые используют какие-то иные подходы. Таким образом мы избавляемся от еще одной ситуации, когда FK может быть полезен — удаление юзера.

DELETE user; -- Не возможно, потому что мы не удаляем юзеров 

Валидация на бекенде

У нас остается только 2 операции, в которых FK может выдать ошибку:

INSERT INTO order;
UPDATE order;

Например, мы создаем заказ, а юзера с указанным ID не существует. Но может ли такое произойти? Конечно нет.
Например, если запрос на создание заказа отправляется на бэкенд самим пользователем, то мы получаем его айдишник из авторизации, из JWT токенов. Как мы обсуждали ранее - айдишники иммутабельны, поэтому мы можем не бояться, что пользователь удалится, либо поменяет айди.

Аналогично с обновлением заказа, хотя тут проще - не понятно как создатель заказа может поменяться. А даже если и может - проверка валидности user_id все равно должна находиться на стороне бэкенда.

Итог

В правильно спроектированном бэкенде не будет ситуации, когда FK может триггернуться и выдать ошибку. Таким образом FK не дают нам каких-либо преимуществ. При этом важно не забывать, что FK - не бесплатные, они чуть-чуть, но нагружают СУБД.

-- Операции, на которых FK может выдать ошибку
UPDATE user SET id = ?; -- Не возможно из-за иммутабельности ID 
DELETE user; -- Не удаляем юзеров 

INSERT INTO order; -- В любом случае проверяем на бекенде user_id 
UPDATE order SET user_id = ?; -- user_id не может поменяться

Стоит ли после прочтения этой статьи бежать и удалять FK из всех таблиц с прода? Нет конечно, скорее всего вы не заметите разницы в перформансе.

В принципе эта статья скорее не про то "как надо и не надо делать", а так, пофлудить :)

Для душнил

Ниже описаны ответы на вопросы, которые у вас могут возникнуть при прочтении статьи

В смысле мы данные не удаляем?

А как же ФЗ-152?

По законам о персональных данных мы должны удалять персональные данные пользователей, например, по их просьбе. Но мы не обязаны удалять все данные, только персональные. В нашем случае достаточно занулить телефон и почту:

UPDATE user SET phone = null, email = null WHERE id = ?;

Мы же не будем удалять заказы, чеки, платежи и так далее -_-.

А как же архивирование?

Что делать, если наша БД разрослась, и нам нужно удалить, например, старые платежи или хотя бы куда-то их перенести.
Скажем, есть таблица payment:

CREATE TABLE "payment"(  
  id UUID PRIMARY KEY,  
  status TEXT NOT NULL,    
  
  order_id UUID NOT NULL  -- И другие поля
);

Мы хотим заархивировать заказы и платежи

Архивация?

Под архивацией имеется ввиду перенос в более медленное либо холодное хранилище, например, из Postgres в Clickhouse

Нуууу....
Тогда нам точно не нужны FK? Наша реализация с soft-delete и отсутствием FK никак не помешает архивации, а FK, как и всегда, не дадут нам каких-либо преимуществ.

Что если кто-то зайдет в БД?

Если ваши джуны постоянно шастают по продакшен БД — я вам сочувствую. Но в таком случае даже лучше если не будет FK, потому что лучше джун удалит 1 запись из user, а не 1 запись из user и все связанные из order :)

В идеальном мире права на запись в продакшене должны быть у тимлида/девопса, а на удаление — ни у кого. Но даже имея FK можно накосячить с айдишниками, например, заменить один валидный order.user_id на другой, тоже валидный.

P.S.

Как подмечают в комментариях — FK может помочь защититься от ошибок со стороны неправильно работающего бэкенда, скриптов и тд.

С вами я скорее соглашусь. Да, FK не панацея, но его можно использовать как дополнительную защиту от ошибок.