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

Вітаю на одній з ключових лекцій нашого курсу! Сьогодні поговоримо про те, як створювати зовнішні ключі в PostgreSQL. Ця тема реально важлива в проєктуванні баз даних, бо саме зовнішні ключі дозволяють організувати зв'язки між таблицями. Якщо відчуваєш, що скоро заблукаєш у своєму майбутньому "SQL-місті", то уяви зовнішні ключі як мости, що з'єднують різні райони.

Якщо простими словами, зовнішній ключ — це стовпець (або набір стовпців) в одній таблиці, який посилається на стовпець (зазвичай первинний ключ) іншої таблиці.

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

Чому це важливо?

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

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

Створити зовнішній ключ у PostgreSQL легко — достатньо трохи SQL-магії. Ось базовий синтаксис:

CREATE TABLE zalezhna_tablytsya (
    kolonka_foreign_id DATA_TYPE REFERENCES batkivska_tablytsya(kolonka_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 ('Ivan Ivanov'), ('Mariia Smyrnova');

-- Додаємо курси
INSERT INTO courses (title) VALUES ('Matematika'), ('Fizyka');

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

Вибірка даних

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

-- Курси, на які записаний Ivan Ivanov
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Студенти, записані на курс "Matematika"
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 ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Matematika', 1), ('Fizyka', 1);

-- Видалення студента Ivanova
DELETE FROM students WHERE student_id = 1;

-- Таблиця courses тепер буде порожньою, бо всі курси, пов'язані з Ivanovym, видалені

Ще раз і більш детально ми розберемо цю тему у наступній лекції.

Процес валідації даних при використанні зовнішніх ключів

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

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

Приклад: спроба вставити некоректні дані

-- Спроба записати курс на неіснуючого студента
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Pomylka: porushennia obmezhennia zovnishnoho kliucha

Типові помилки при створенні зовнішніх ключів

1. Відсутність індексу на зовнішньому ключі. PostgreSQL автоматично створює індекс для первинного ключа, але не для зовнішнього. Якщо ти часто будеш використовувати зовнішній ключ в умовах WHERE, варто створити індекс вручну.
2. Невірний порядок створення таблиць. Не можна створити зовнішній ключ, що посилається на таблицю, якої ще не існує.
3. Забутий модифікатор ON DELETE або ON UPDATE. Це може призвести до неочікуваної поведінки при редагуванні даних.

Тепер, коли ти знаєш, як створювати зовнішні ключі, ти отримав потужний інструмент для створення структурованих і узгоджених баз даних. У наступній лекції ми детальніше розглянемо дії ON DELETE CASCADE і ON UPDATE RESTRICT для керування пов'язаними даними.