Przykłady SQL używają PostgreSQL, chyba że wymieniono inny dialekt. Krótkie fragmenty zakładają, że tabele, do których się odwołują, już istnieją. Zweryfikuj DDL w docelowej bazie danych przed jego zastosowaniem.
Zrozumienie kluczy głównych
A klucz główny to kolumna — lub zestaw kolumn — która jednoznacznie identyfikuje każdy wiersz w tabeli bazy danych. Żadne dwa wiersze nie mogą mieć tej samej wartości klucza głównego, a wartość ta nigdy nie może być NULL. Tabela może deklarować co najwyżej jeden klucz podstawowy, a wybór jednego jest dobrą praktyką dla tabel aplikacji.
Klucze naturalne kontra klucze zastępcze
A klucz naturalny używa danych, które już istnieją w domenie świata rzeczywistego. Na przykład kod ISO kraju (US, FR) lub numer ISBN książki. Klucze naturalne mają znaczenie, ale mogą zmieniać się w czasie i mogą wiązać się z obawami dotyczącymi prywatności (pomyśl o numerach ubezpieczenia społecznego).
A klucz zastępczy to sztuczny identyfikator bez znaczenia biznesowego — zazwyczaj automatycznie inkrementowana liczba całkowita lub UUID. Klucze zastępcze są stabilne, kompaktowe i łatwe do indeksowania, dlatego większość nowoczesnych schematów je preferuje.
-- Natural key example
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- Surrogate key example
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMPTZ DEFAULT now()
);Zrozumienie kluczy obcych
A klucz obcy to kolumna (lub grupa kolumn) w jednej tabeli, która odwołuje się do klucza głównego lub innego kwalifikującego się klucza unikalnego, w tym klucza w tej samej tabeli. Wymusza integralność referencyjna: wartość klucza obcego inna niż null musi mieć pasujący wiersz odniesienia. Klucze obce dopuszczające wartości null oraz klucze złożone wymagają jawnych reguł dopuszczalności null i dopasowania.
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT NOT NULL,
CONSTRAINT fk_posts_author
FOREIGN KEY (author_id) REFERENCES users (id)
);Z tym ograniczeniem baza danych odrzuci każde INSERT na posts gdzie author_id nie pasuje do istniejącego users.id.
Jak klucze podstawowe i obce współpracują ze sobą
Klucze główne i obce stanowią kręgosłup relacyjnych baz danych. Klucz główny w jednej tabeli staje się kotwicą; klucz obcy w powiązanej tabeli wskazuje z powrotem na tę kotwicę. To połączenie wymusza zadeklarowaną relację. Możesz również JOIN tabele i modeluj rzeczywiste relacje, takie jak „użytkownik ma wiele posty" lub "zamówienie należy do klienta."
-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM posts p
JOIN users u ON u.id = p.author_id;Aby naszkicować encje przed napisaniem SQL, otwórz Kreator ERD. Aby sprawdzić powyższe definicje tabel i kluczy obcych, wklej je do Edytor SQL do ERD.
Klucze złożone
Czasami jedna kolumna nie wystarczy, aby zagwarantować unikalność. klucz złożony (nazywany również klucz złożony) łączy dwie lub więcej kolumn w jeden klucz główny. Ten wzorzec jest powszechny w tabele łączące (znane również jako tabele łączące lub tabele asocjacyjne) używane w relacjach wiele-do-wielu.
-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_at TIMESTAMPTZ DEFAULT now(),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (course_id) REFERENCES courses (id)
);Złożony klucz podstawowy (student_id, course_id) zapewnia, że ten sam student nie może zapisać się na ten sam kurs dwa razy, jednocześnie pozwalając każdemu studentowi na branie udziału w wielu kursach, a każdemu kursowi na posiadanie wielu studentów.
Akcje kaskadowe
Definiując klucz obcy, możesz również określić, co się stanie, gdy odwołujący się wiersz zostanie zaktualizowany lub usunięty. Te akcje referencyjne utrzymuj spójność danych bez konieczności ręcznego czyszczenia.
- CASCADE — automatycznie usuwaj lub aktualizuj wiersze potomne, gdy wiersz nadrzędny ulegnie zmianie.
- SET NULL — ustaw kolumnę klucza obcego na
NULL(kolumna musi dopuszczać wartości null). - SET DEFAULT — zresetuj kolumnę do jej wartości domyślnej, która nadal musi spełniać klucz obcy. Wsparcie różni się w zależności od silnika; MySQL InnoDB odrzuca tę akcję.
- RESTRICT / NO ACTION — odrzuć zmianę, która pozostawia nieprawidłowe odwołania. W PostgreSQL NO ACTION może zostać odroczone, gdy ograniczenie jest odraczalne; RESTRICT jest natychmiastowe.
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
body TEXT NOT NULL,
post_id INT NOT NULL,
CONSTRAINT fk_comments_post
FOREIGN KEY (post_id)
REFERENCES posts (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
-- Deleting a post now automatically removes all its comments.Użyj CASCADE ostrożnie — usunięcie wiersza nadrzędnego po cichu usunie każdy powiązany wiersz podrzędny. W systemach z intensywnym audytem rozważ RESTRICT zamiast tego połączone z flagą soft-delete.
Najlepsze praktyki
- Preferuj klucze zastępcze (
SERIAL,BIGSERIAL, lubUUID) jako klucze główne dla tabel aplikacji. Użyj klucza naturalnego, gdy identyfikator domeny jest wystarczająco stabilny dla Twoich wymagań, i zaplanuj, jak zmiany wpłyną na referencje. - Zawsze nazwij swoje ograniczenia (np.,
fk_posts_author) aby komunikaty o błędach były zrozumiałe. - Indeksuj kolumny klucza obcego. Wiele baz danych nie tworzy automatycznie indeksu dla klucza obcego, a brakujące indeksy mogą znacznie spowolnić złączenia i usuwanie kaskadowe.
- Pomyśl o akcja kaskadowa dla każdego klucza obcego. Domyślnie ustawione na
NO ACTIONjest najbezpieczniejsze; przełącz naCASCADEtylko wtedy, gdy wiersze podrzędne faktycznie tracą znaczenie bez swojego rodzica. - Wybierz klucze złożone na podstawie reguł tożsamości modelu. Klucz zastępczy może uprościć odwołania, ale zachowaj ograniczenie UNIQUE dla oryginalnego klucza biznesowego, gdy należy zapobiegać duplikatom.
- Najpierw wymodeluj swój schemat wizualnie. Ten Kreator ERD pozwala naszkicować model koncepcyjny i przejrzeć jego konwersję na SQL — wyłapując błędy projektowe przed trafieniem na produkcję.



