Klucze obce: FOREIGN KEY

Wyobraź sobie, że masz dwie tabele: jedną z danymi o studentach i drugą z danymi o kursach. Jak myślisz, jak można zapisać informację o tym, którzy studenci są zapisani na które kursy? Możesz dodać kolumnę w tabeli studentów do przechowywania wszystkich kursów, na które są zapisani, ale to szybko zrobi się bałagan. Jednym z najlepszych rozwiązań jest użycie kluczy obcych!

Klucz obcy (FOREIGN KEY) — to kolumna lub zestaw kolumn, które służą do tworzenia powiązania między tabelami. Wskazuje na kolumnę (zwykle PRIMARY KEY) w innej tabeli, zapewniając spójność referencyjną danych między nimi. Więc jeśli jakiś student jest zapisany na kurs, możemy być pewni, że ten kurs naprawdę istnieje.

Klucze obce:

• Pomagają utrzymać spójność danych, gwarantując, że rekordy w jednej tabeli odpowiadają danym w innej.
• Robią bazę danych czytelną i logicznie uporządkowaną.
• Ułatwiają pracę z danymi, zapobiegają duplikatom i błędom.

Przykład z życia codziennego

Wyobraź sobie bibliotekę. Tabela books zawiera listę książek, a tabela members — listę czytelników. Żeby śledzić, kto wypożyczył jaką książkę, możemy stworzyć tabelę borrowed_books, która będzie odnosić się do books i members przez klucze obce. Dzięki temu za każdym razem, gdy ktoś bierze książkę, dokładnie wiemy, kto to był i jaką książkę wypożyczył.

Typy powiązań między tabelami

Klucze obce pozwalają opisywać różne powiązania między tabelami. Zobaczmy trzy podstawowe typy:

1. Powiązanie "Jeden do Jednego" (ONE-TO-ONE)

To najprostsze powiązanie, gdzie jeden rekord w jednej tabeli odpowiada dokładnie jednemu rekordowi w drugiej tabeli. Na przykład tabela users może zawierać info o użytkownikach, a tabela profiles może mieć dodatkowe dane, takie jak adresy czy zdjęcia.

Przykład SQL dla powiązania 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), -- odnosi się do users.user_id
    address TEXT
);

Tutaj kolumna user_id w tabeli profiles działa jako klucz obcy, wskazujący na user_id w tabeli users.

Przykład tabeli users:

Przykład tabeli profiles:

2. Powiązanie "Jeden do Wielu" (ONE-TO-MANY)

To najpopularniejsze powiązanie. Na przykład jeden klient może złożyć wiele zamówień. W tym przypadku tabela customers będzie powiązana z tabelą orders przez klucz obcy.

Przykład SQL dla powiązania 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),	-- odnosi się do customers.customer_id
    order_date DATE NOT NULL
);

Tablica orders może mieć wiele rekordów wskazujących na jeden rekord w tabeli customers

Przykład tabeli customers:

Przykład tabeli orders:

3. Powiązanie "Wiele do Wielu" (MANY-TO-MANY)

Czasem jeden rekord w tabeli może być powiązany z wieloma rekordami w innej tabeli i na odwrót. Na przykład studenci zapisują się na kursy, a na jednym kursie może być wielu studentów. Do tego zwykle tworzy się tabelę pośrednią.

Przykład SQL dla powiązania MANY-TO-MANY:

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

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

-- "zapis konkretnego studenta na konkretny kurs"
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id), -- odniesienie do id-studenta
    course_id INT REFERENCES courses(course_id),	-- odniesienie do id-kursu
    PRIMARY KEY (student_id, course_id)
);

Tutaj tabela enrollments pełni rolę "mostu" między tabelami students i courses.

Przykład tabeli students:

Przykład tabeli courses:

Przykład tabeli enrollments:

Zalety używania kluczy obcych

1. Dane pozostają spójne. Jeśli klucz obcy wskazuje na nieistniejący rekord, PostgreSQL nie pozwoli na wstawienie lub aktualizację danych.
2. Ułatwienie pracy z tabelami. Klucze obce pozwalają łatwo łączyć dane między tabelami i wykonywać złożone zapytania, takie jak JOIN.
3. Automatyczne zachowanie. Możesz ustawić akcje przy usuwaniu lub aktualizacji powiązanych rekordów (np. kaskadowe usuwanie, ustawienie NULL itd.).

Jak wygląda klucz obcy w PostgreSQL?

Składnia do tworzenia klucza obcego podczas tworzenia tabeli:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- klucz obcy-odniesienie
);

Tutaj:

• customer_id — to klucz obcy.
• Odnosi się do customer_id w tabeli customers.

Możesz też jawnie wskazać klucz obcy za pomocą FOREIGN KEY:

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

Ciekawostka

Samo pojęcie "klucz obcy" (FOREIGN KEY) pochodzi z teorii relacyjnych baz danych opracowanej w latach 70. przez Edgara Codda. Niesamowite, ale ta podstawowa idea jest tak mocna, że do dziś jest kluczowym elementem współczesnych baz danych. Kto powiedział, że klasyka jest nieaktualna?

Teraz już wiesz, czym jest klucz obcy i dlaczego jest taki ważny. Przed Tobą jeszcze nauka, jak je tworzyć przy zakładaniu tabel i jak poprawnie używać operacji kaskadowych. Ale to już w kolejnych wykładach!