Внешние ключи — Проектирование баз данных

• Внешний ключ
• Выводы

В этом уроке познакомимся с внешними ключами и как они помогают создавать связь между таблицами.

Внешний ключ

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

CREATE TABLE users (
  id bigint PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  birthday DATE,
  email_confirmed BOOLEAN,
  email VARCHAR(255) UNIQUE NOT NULL,
  gender VARCHAR(255) NOT NULL,
  password_digest VARCHAR(255) NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE topics (
  id bigint PRIMARY KEY,
  -- таблица topics связана с таблицей users
  user_id bigint REFERENCES users(id) NOT NULL,
  title varchar(255),
  body text,
  created_at TIMESTAMP NOT NULL
);

INSERT INTO users (birthday, created_at, email, first_name, gender, id, last_name, password_digest, username) VALUES ('2022-05-25 06:39:51.694', '2022-06-14 18:31:05.296', 'Trevion53@yahoo.com', 'Lucienne', 'female', 1, 'Feil', '1111', 'Duncan3');
INSERT INTO users (birthday, created_at, email, first_name, gender, id, last_name, password_digest, username) VALUES ('2022-01-13 22:38:14.676', '2022-06-14 02:04:13.104', 'Baylee52@yahoo.com', 'Ramiro', 'female', 2, 'Wolf', '1111', 'Michaela11');

View on DB Fiddle

В таблице topics атрибут user_id связан с атрибутом id таблицы users. Синтаксически для этого добавляется ключевое слово REFERENCES после типа поля. Затем добавляется имя таблицы, на которую идет связь. В скобках указывается поле, с которым эта связь создается.

• Если попробовать вставить в topics запись со значением user_id, которого нет в таблице users, то возникнет ошибка

  -- Первые два запроса выполнятся
  INSERT INTO topics (body, created_at, id, title, user_id) VALUES ('Architecto id autem modi.', '2022-06-14 12:09:50.532', 1, 'est iste corporis', 1);
  INSERT INTO topics (body, created_at, id, title, user_id) VALUES ('Eum aut dolore aut molestias aliquid quidem. Qui sit id vel id aut aliquam et facilis numquam. Expedita laboriosam aut sit dolor quia perspiciatis dicta dolores nihil. Dolorem maiores consequuntur assumenda similique eius quis nostrum. Dolor maiores natus. Maiores labore debitis incidunt libero excepturi velit porro.', '2022-06-14 13:20:33.943', 2, 'eveniet aut facere', 2);
  
  INSERT INTO topics (body, created_at, id, title, user_id) VALUES ('Velit quia molestiae doloremque velit rerum odit ea. Voluptatem esse provident. Ullam voluptates cumque omnis dolor illum reiciendis.', '2022-06-14 06:11:14.373', 4, 'et ut quos', 3);
  -- Query Error: error: insert or update on table "topics" violates foreign key constraint "topics_user_id_fkey"
  

  View on DB Fiddle

• Если попытаться удалить запись из users, на которую есть ссылки из таблицы topics, то возникнет ошибка

  DELETE FROM users WHERE id = 1;
  -- Query Error: error: update or delete on table "users" violates foreign key constraint "topics_user_id_fkey" on table "topics"
  

  View on DB Fiddle

Внешний ключ проверяет целостность данных и не позволяет появляться «мусору». Поэтому лучше ставить внешние ключи на все идентификаторы, для которых есть родительская таблица в базе.

Внешние ключи поддерживают несколько вариаций. Таким образом они поддерживают целостность, но не приводят к ошибкам при попытке удалить запись.

Вариант 1 — Каскадное удаление:

CREATE TABLE topics (
    user_id bigint REFERENCES users(id) ON DELETE CASCADE,
);

View on DB Fiddle

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

Вариант 2 — Записи остаются, но вместо идентификатора проставляется null:

CREATE TABLE topics (
  user_id bigint REFERENCES users(id) ON DELETE SET NULL,
);

View on DB Fiddle

Используется нечасто, но иногда может быть полезно.

Выводы

В этом уроке мы узнали про внешние ключи. Они обеспечивают связь между двумя таблицами в базе данных. Повторим ключевые моменты:

• Для создания связи между таблицами в колонке указывают ключевое слово REFERENCES, название таблицы и колонку, с которой необходима связь
• Внешний ключ может предотвратить удаление данных, если на эти данные ссылаются из других таблиц.
• При помощи ключевого слова ON DELETE можно настроить каскадное удаление, либо запись NULL в связанных полях.
• При использовании внешних ключей необходимо учитывать возможность возникновения ошибок при вставке или удалении данных. Поэтому важно хорошо продумывать структуру связей между таблицами и выбирать подходящие действия при удалении связанных данных (CASCADE, SET NULL и др.).

Таким образом, использование первичных и внешних ключей в SQL помогает не только организовать базу данных, но и обеспечить целостность и надежность данных в ней.

Самостоятельная работа

По ссылке вы можете открыть базу с таблицами, которые были в теории.

Поэкспериментируйте со структурой БД. Добавьте каскадное удаление для таблицы topics.

Дополнительные материалы

1. Официальная документация по ограничениям