Tworzenie kluczy obcych podczas tworzenia tabel

Wbijaj na jeden z kluczowych wykładów naszego kursu! Dzisiaj pogadamy o tym, jak tworzyć klucze obce w PostgreSQL. Ten temat jest mega ważny przy projektowaniu baz danych, bo właśnie klucze obce pozwalają ogarnąć relacje między tabelami. Jeśli czujesz, że zaraz się zgubisz w swoim przyszłym "SQL-mieście", to wyobraź sobie klucze obce jako mosty łączące różne dzielnice.

Mówiąc po ludzku, klucz obcy to kolumna (albo zestaw kolumn) w jednej tabeli, która wskazuje na kolumnę (zwykle primary key) w innej tabeli.

Na przykład, jeśli masz dwie tabele — students (studenci) i courses (kursy), to klucz obcy w tabeli courses może "wskazywać", do którego studenta przypisany jest kurs. W ten sposób powstaje relacja między tymi tabelami.

Dlaczego to ważne?

1. Klucz obcy pomaga zapewnić integralność danych: nie możesz wrzucić czegoś do jednej tabeli, jeśli tego nie ma w drugiej.
2. Ułatwiają pracę z danymi. Na przykład, jak usuwasz rekord w jednej tabeli, możesz ustawić automatyczne usuwanie powiązanych rekordów w drugiej.

Składnia tworzenia klucza obcego

Stworzenie klucza obcego w PostgreSQL to bułka z masłem — wystarczy trochę SQL-magii. Oto podstawowa składnia:

CREATE TABLE zależna_tabela (
    kolumna_foreign_id DATA_TYPE REFERENCES rodzicielska_tabela(kolumna_id)
);

Lepiej zanurkujmy w szczegóły i ogarnijmy parę przykładów.

Przykład 1: Tabele students i courses

Wyobraź sobie, że chcemy stworzyć relację między studentami a kursami. Każdy kurs powinien być przypisany do jakiegoś studenta. Do tego wystarczy taki zapytanie:

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)
);

Tutaj:

• W tabeli students stworzyliśmy primary key PRIMARY KEY do identyfikacji każdego studenta.
• W tabeli courses kolumna student_id jest kluczem obcym FOREIGN KEY, który wskazuje na kolumnę student_id w tabeli students.

Tabela students

Tabela courses

Ważna uwaga

Kiedy dodajesz klucz obcy, PostgreSQL automatycznie tworzy regułę, która sprawdza, czy wartości w kluczu obcym zgadzają się z istniejącymi wartościami w wskazanej tabeli. Jeśli spróbujesz wstawić złą wartość, baza danych wywali błąd.

Praktyczne zastosowanie: model students, courses i enrollments

Ogarnijmy bardziej złożony przykład z relacją „wiele-do-wielu”. Ci sami studenci mogą zapisywać się na kilka kursów, a jeden kurs może mieć wielu studentów. Do takiej relacji potrzebujemy tabeli pośredniej.

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), -- klucz obcy
    course_id INT REFERENCES courses(course_id)		-- klucz obcy
);

Tutaj tabela enrollments łączy tabele students i courses używając kluczy obcych student_id i course_id.

Wstawianie danych

-- Dodajemy studentów
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- Dodajemy kursy
INSERT INTO courses (title) VALUES ('Matematyka'), ('Fizyka');

-- Zapisujemy studentów na kursy
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Wybieranie danych

Teraz możemy łatwo robić zapytania, żeby sprawdzić, na jakie kursy zapisany jest student albo którzy studenci są zapisani na konkretny kurs:

-- Kursy, na które zapisany jest Ivan Ivanov
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Studenci zapisani na kurs "Matematyka"
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Zaawansowane opcje: ON DELETE i ON UPDATE

Klucz obcy powinien też ogarniać zachowanie tabeli przy zmianie lub usuwaniu rekordów w tabeli rodzica. Do tego są modyfikatory ON DELETE i ON UPDATE. Oto główne opcje:

• CASCADE: zmiany lub usunięcia w tabeli rodzica automatycznie przechodzą na powiązane rekordy w tabeli dziecka.
• SET NULL: wartości klucza obcego w tabeli dziecka ustawiają się na NULL.
• RESTRICT: blokuje usunięcie lub zmianę danych, jeśli są już używane w zależnej tabeli dziecka.
• NO ACTION: w sumie to samo co RESTRICT, ale sprawdzane później.

Przykład 2: Użycie ON DELETE CASCADE

Wyobraź sobie, że chcemy, żeby usunięcie studenta z tabeli students automatycznie usuwało wszystkie jego kursy z tabeli courses. Tak to zrobić:

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
);

Teraz, jeśli usuniesz studenta z tabeli students, wszystkie rekordy powiązane z tym studentem w tabeli courses też zostaną usunięte. Na przykład:

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Matematyka', 1), ('Fizyka', 1);

-- Usuwanie studenta Ivanova
DELETE FROM students WHERE student_id = 1;

-- Tabela courses będzie teraz pusta, bo wszystkie kursy powiązane z Ivanovem zostały usunięte

Jeszcze raz i bardziej szczegółowo ogarniemy ten temat w następnym wykładzie.

Proces walidacji danych przy użyciu kluczy obcych

Kiedy tworzysz klucz obcy, PostgreSQL staje się takim surowym portierem, który sprawdza każdy nowy rekord. Na przykład:

• Jeśli próbujesz wstawić rekord z nieistniejącą wartością klucza obcego, dostaniesz błąd.
• Jeśli usuwasz rekord w tabeli rodzica, który jest powiązany z innymi tabelami (bez ON DELETE CASCADE), to wywoła naruszenie integralności danych.

Przykład: próba wstawienia niepoprawnych danych

-- Próba zapisania kursu na nieistniejącego studenta
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Błąd: naruszenie ograniczenia klucza obcego

Typowe błędy przy tworzeniu kluczy obcych

1. Brak indeksu na kluczu obcym. PostgreSQL automatycznie tworzy indeks dla primary key, ale nie dla klucza obcego. Jeśli często będziesz używać klucza obcego w warunkach WHERE, warto zrobić indeks ręcznie.
2. Zła kolejność tworzenia tabel. Nie możesz stworzyć klucza obcego, który wskazuje na tabelę, która jeszcze nie istnieje.
3. Zapomniany modyfikator ON DELETE albo ON UPDATE. To może prowadzić do dziwnego zachowania przy edycji danych.

Teraz, kiedy już wiesz, jak tworzyć klucze obce, masz w rękach potężne narzędzie do budowania uporządkowanych i spójnych baz danych. W następnym wykładzie dokładniej ogarniemy akcje ON DELETE CASCADE i ON UPDATE RESTRICT do zarządzania powiązanymi danymi.