Зовнішні ключі: FOREIGN KEY

Уяви, що в тебе є дві таблиці: одна з даними про студентів і ще одна з даними про курси. Як думаєш, як можна показати, які студенти записані на які курси? Можна додати стовпець у таблицю студентів для зберігання всіх курсів, на які вони записані, але це швидко перетвориться на хаос. Одне з найкращих рішень — це використати зовнішні ключі!

Зовнішній ключ (FOREIGN KEY) — це стовпець або набір стовпців, який використовується для створення зв'язку між таблицями. Він вказує на стовпець (зазвичай PRIMARY KEY) в іншій таблиці, забезпечуючи посилальну цілісність даних між ними. Тобто, якщо якийсь студент записаний на курс, ми можемо бути впевнені, що цей курс реально існує.

Зовнішні ключі:

• Допомагають підтримувати цілісність даних, гарантуючи, що записи в одній таблиці відповідають даним в іншій.
• Роблять базу даних зрозумілою і логічно організованою.
• Спрощують обробку даних, запобігають дублюванню і помилкам.

Приклад з реального життя

Уяви бібліотеку. Таблиця books містить список книжок, а таблиця members — список читачів. Щоб відслідковувати, хто взяв яку книжку, можна створити таблицю borrowed_books, яка буде посилатися на books і members через зовнішні ключі. Таким чином, кожного разу, коли хтось бере книжку, ми точно знаємо, хто це був і яку саме книжку він взяв.

Типи зв'язків між таблицями

Зовнішні ключі дозволяють описувати різні зв'язки між таблицями. Давай розберемо три основних типи:

1. Зв'язок "Один-до-Одного" (ONE-TO-ONE)

Це найпростіший зв'язок, де один запис в одній таблиці відповідає рівно одному запису в іншій таблиці. Наприклад, таблиця users може містити інформацію про користувачів, а таблиця profiles може містити додаткову інформацію, таку як їх адреси або фото.

Приклад SQL для зв'язку ONE-TO-ONE:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE TABLE profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(user_id), -- посилається на users.user_id
    address TEXT
);

Тут стовпець user_id у таблиці profiles виступає як зовнішній ключ, що посилається на user_id у таблиці users.

Приклад таблиці users:

Приклад таблиці profiles:

2. Зв'язок "Один-до-Багатьох" (ONE-TO-MANY)

Це найпопулярніший зв'язок. Наприклад, один клієнт може зробити багато замовлень. У цьому випадку таблиця customers буде пов'язана з таблицею orders за допомогою зовнішнього ключа.

Приклад SQL для зв'язку ONE-TO-MANY:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),	-- посилається на customers.customer_id
    order_date DATE NOT NULL
);

Таблиця orders може містити багато записів, що вказують на один запис у таблиці customers

Приклад таблиці customers:

Приклад таблиці orders:

3. Зв'язок "Багато-до-Багатьох" (MANY-TO-MANY)

Іноді один запис у таблиці може бути пов'язаний з кількома записами в іншій таблиці, і навпаки. Наприклад, студенти записуються на курси, а на одному курсі може бути багато студентів. Для цього зазвичай створюється проміжна таблиця.

Приклад SQL для зв'язку MANY-TO-MANY:

-- студенти
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 (
    student_id INT REFERENCES students(student_id), -- посилання на id-студента
    course_id INT REFERENCES courses(course_id),	-- посилання на id-курсу
    PRIMARY KEY (student_id, course_id)
);

Тут таблиця enrollments грає роль "моста" між таблицями students і courses.

Приклад таблиці students:

Приклад таблиці courses:

Приклад таблиці enrollments:

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

1. Дані залишаються послідовними. Якщо зовнішній ключ вказує на неіснуючий запис, PostgreSQL не дозволить вставити або оновити дані.
2. Спрощення роботи з таблицями. Зовнішні ключі дозволяють легко зв'язувати дані між таблицями і виконувати складні запити, такі як JOIN.
3. Автоматична поведінка. Ти можеш налаштувати дії при видаленні або оновленні пов'язаних записів (наприклад, каскадне видалення, встановлення NULL і т.д.).

Як виглядає зовнішній ключ у PostgreSQL?

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

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- зовнішній ключ-посилання
);

Тут:

• customer_id — це зовнішній ключ.
• Він посилається на customer_id у таблиці customers.

Ти також можеш явно вказати зовнішній ключ за допомогою FOREIGN KEY:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Цікавий факт

Сам термін "зовнішній ключ" (FOREIGN KEY) прийшов з теорії реляційних баз даних, розробленої у 1970-х роках Едгаром Коддом. Дивно, але основна ідея настільки потужна, що досі є ключовим елементом сучасних баз даних. Хто сказав, що класика не актуальна?

Тепер ти розумієш, що таке зовнішній ключ і чому він такий важливий. Тобі ще належить дізнатися, як створювати їх при створенні таблиць і як правильно використовувати каскадні операції. Але це вже у наступних лекціях!