Внешние ключи и ссылочная целостность

Связываем таблицы внешними ключами и заставляем базу саму охранять ссылочную целостность.

Внешний ключ (FOREIGN KEY) — столбец, значения которого обязаны ссылаться на существующую строку в другой (или той же) таблице.

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

Представьте таблицы users и orders: у заказа есть user_id. Если ничто не следит за связью, можно создать заказ с user_id = 999, которого нет, — получится «висячая» ссылка. Внешний ключ запрещает такое: база проверяет, что user_id указывает на реальную строку в users. Это и есть ссылочная целостность.

Важно: внешние ключи работают только в InnoDB. В MyISAM объявление ключа молча игнорируется — ещё одна причина выбирать InnoDB.

Объявление внешнего ключа

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

ON DELETE и ON UPDATE

Что делать, если удаляют пользователя, у которого есть заказы? Поведение задаётся опциями:

Опция	Что происходит при удалении родителя
`RESTRICT` (по умолчанию)	запретить удаление, пока есть ссылки
`CASCADE`	удалить вместе с ним и связанные строки
`SET NULL`	обнулить ссылку (столбец должен допускать NULL)

FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Демонстрация на песочнице

SQLite тоже понимает внешние ключи. Соберём связанные таблицы и сделаем выборку через JOIN — она покажет, как связь работает на чтении:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  total INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (name) VALUES ('Анна'), ('Борис');
INSERT INTO orders (user_id, total) VALUES (1, 500), (1, 300), (2, 700);

SELECT u.name, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY u.name;

Вывод:

name   total
Анна   500
Анна   300
Борис  700

Нормализация одной фразой

Внешние ключи — техническая опора нормализации: вместо того чтобы дублировать имя пользователя в каждой строке заказов, мы храним его один раз в users, а в orders держим только ссылку user_id. Меньше дублей — меньше рассогласований.

Итог

Внешний ключ заставляет значение ссылаться на реальную строку в другой таблице.
Это обеспечивает ссылочную целостность — нет «висячих» ссылок.
Работает только в InnoDB; в MyISAM игнорируется.
ON DELETE CASCADE / SET NULL / RESTRICT задают поведение при удалении родителя.