Создание внешних ключей при создании таблиц

Добро пожаловать на одну из ключевых лекций нашего курса! Сегодня мы поговорим о том, как создавать внешние ключи в PostgreSQL. Эта тема имеет решающее значение в проектировании баз данных, поскольку именно внешние ключи позволяют организовать связи между таблицами. Если вы чувствуете, что скоро заблудитесь в вашем будущем "SQL-городе", то представьте внешние ключи как мосты, соединяющие разные районы.

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

Например, если у вас есть две таблицы — students (студенты) и courses (курсы), то внешний ключ в таблице courses может "указывать", на кого из студентов записан курс. Таким образом, создаётся связь между этими таблицами.

Почему это важно?

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

Синтаксис создания внешнего ключа

Создать внешний ключ в PostgreSQL легко — достаточно немного SQL-магии. Вот базовый синтаксис:

CREATE TABLE зависимая_таблица (
    колонка_foreign_id DATA_TYPE REFERENCES родительская_таблца(колонка_id)
);

Давайте лучше погрузимся в нюансы и разберем пару примеров.

Пример 1: Таблицы students и courses

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

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

Здесь:

• В таблице students мы создали первичный ключ PRIMARY KEY для идентификации каждого студента.
• В таблице courses столбец student_id является внешним ключом FOREIGN KEY, который ссылается на столбец student_id в таблице students.

Таблица students

Таблица courses

Важное замечание

Когда вы добавляете внешний ключ, PostgreSQL автоматически создаёт правило, которое проверяет, чтобы значения во внешнем ключе совпадали с существующими значениями в указанной таблице. Если вы попытаетесь вставить неверное значение, база данных выдаст ошибку.

Практическое применение: модель students, courses и enrollments

Рассмотрим более сложный пример с отношением «многие-ко-многим». Одни и те же студенты могут записываться на несколько курсов, а один курс может посещать множество студентов. Для создания такой связи нам потребуется промежуточная таблица.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- внешний ключ
    course_id INT REFERENCES courses(course_id)		-- внешний ключ
);

Здесь таблица enrollments связывает таблицы students и courses с использованием внешних ключей student_id и course_id.

Вставка данных

-- Добавляем студентов
INSERT INTO students (name) VALUES ('Иван Иванов'), ('Мария Смирнова');

-- Добавляем курсы
INSERT INTO courses (title) VALUES ('Математика'), ('Физика');

-- Записываем студентов на курсы
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Выборка данных

Теперь мы можем легко выполнять запросы, чтобы узнать, какие курсы посещает студент или какие студенты записаны на конкретный курс:

-- Курсы, на которые записан Иван Иванов
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Студенты, записанные на курс "Математика"
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Расширенные возможности: ON DELETE и ON UPDATE

Внешний ключ также должен управлять поведением таблицы при изменении или удалении записей в родительской таблице. Для этого используют модификаторы ON DELETE и ON UPDATE. Вот основные опции:

• CASCADE: изменения или удаления в родительской таблице автоматически применяются к дочерним записям.
• SET NULL: значения внешнего ключа в дочерней таблице устанавливаются в NULL.
• RESTRICT: запрещает удаление или изменение данных, если они уже используются в зависимой дочерней таблице.
• NO ACTION: по сути, аналогично RESTRICT, но проверка выполняется позже.

Пример 2: Применение ON DELETE CASCADE

Представьте, что мы хотим сделать так, чтобы удаление студента из таблицы students автоматически удаляло все его курсы из таблицы courses. Вот как это сделать:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

Теперь, если вы удалите студента из таблицы students, все записи, связанные с этим студентом в таблице courses, также будут удалены. Например:

INSERT INTO students (name) VALUES ('Иван Иванов');
INSERT INTO courses (title, student_id) VALUES ('Математика', 1), ('Физика', 1);

-- Удаление студента Иванова
DELETE FROM students WHERE student_id = 1;

-- Таблица courses теперь будет пустой, так как все курсы, связанные с Ивановым, удалены

Еще раз и более подробнее мы разберем эту тему в следующей лекции.

Процесс валидации данных при использовании внешних ключей

Когда вы создаёте внешний ключ, PostgreSQL принимает на себя роль строгого вахтёра, проверяя каждую новую запись. Например:

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

Пример: попытка вставить некорректные данные

-- Попытка записать курс на несуществующего студента
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Ошибка: нарушение ограничения внешнего ключа

Типичные ошибки при создании внешних ключей

1. Отсутствие индекса на внешнем ключе. PostgreSQL автоматически создаёт индекс для первичного ключа, но не для внешнего. Если вы часто будете использовать внешний ключ в условиях WHERE, стоит создать индекс вручную.
2. Неверный порядок создания таблиц. Нельзя создать внешний ключ, ссылающийся на таблицу, которой ещё не существует.
3. Забыт модификатор ON DELETE или ON UPDATE. Это может привести к неожиданному поведению при редактировании данных.

Теперь, когда вы знаете, как создавать внешние ключи, вы получили мощный инструмент для создания структурированных и согласованных баз данных. В следующей лекции мы будем более подробно разбирать действия ON DELETE CASCADE и ON UPDATE RESTRICT для управления связанными данными.