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 wzorców relacji SQL
Relacyjne bazy danych czerpią swoją moc z połączeń między tabelami. Niezależnie od tego, czy projektujesz małą aplikację, czy korporacyjną hurtownię danych, wybór właściwego wzorca relacji określa, jak efektywnie możesz przechowywać, odpytywać i utrzymywać swoje dane. Trzy podstawowe wzorce—jeden do jednego, jeden do wielu, oraz wiele do wielu—obejmują wiele powszechnych powiązań; relacje rekurencyjne i wyższego stopnia wymagają dodatkowego modelowania.
W tym przewodniku omawiamy każdy wzorzec na przykładach z PostgreSQL, badamy rolę tabel łączących i pokazujemy, jak te relacje wyglądają na diagramie encji i relacji (ERD). Jeśli chcesz teraz zwizualizować własny schemat, otwórz Edytor Erd Go i wklejanie obsługiwane CREATE TABLE definicje do wygenerowania diagramu.
Relacje jeden do wielu
A jeden do wielu Relacja (1:N) jest najczęstszym wzorcem w projektowaniu baz danych. Oznacza to, że pojedynczy wiersz w rodzic tabela może być przywoływana przez wiele wierszy w podrzędny tabela, a niepusty klucz obcy potomny wskazuje na jednego rodzica. Klucz obcy dopuszczający wartość null pozwala na element potomny bez rodzica.
Klasyczny przykład: Działy i Pracownicy
Firma posiada wiele działów, a każdy dział zawiera wielu pracowników. Jednak każdy pracownik należy do dokładnie jednego działu w danym czasie.
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
department_id INT NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);Kluczowe szczegóły implementacji
- Klucz obcy (
department_id) zawsze znajduje się na wiele stronę —employeestabela w tym przypadku. ON DELETE RESTRICTzapobiega przypadkowemu usunięciu działu, do którego wciąż są przypisani pracownicy.ON UPDATE CASCADEzapewnia, że jeśli klucz podstawowy działu ulegnie zmianie, wszystkie wiersze pracowników zostaną zaktualizowane automatycznie.- Dodawanie indeksu na
department_idmoże pomóc w złączeniach i filtrach działów; potwierdź korzyść za pomocą reprezentatywnych danych i planu wykonania.
Zapytania o Dane jeden-do-wielu
-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;Inne codzienne przykłady relacji jeden-do-wielu obejmują autorzy → posty na blogu, customers → orders, oraz kategorie → produkty.
Relacje wiele do wielu
A wiele do wielu Relacja (M:N) istnieje, gdy wiersze na oba strony mogą być powiązane z wieloma wierszami po drugiej stronie. Relacyjne bazy danych nie mogą reprezentować tego bezpośrednio za pomocą pojedynczego klucza obcego, więc wprowadzamy tabela łącząca (nazywany również most, złączenie, lub asocjacyjne tabela).
Klasyczny przykład: Studenci i Kursy
Student może zapisać się na wiele kursów, a każdy kurs może mieć wielu studentów. Tabela łącząca enrollments znajduje się między nimi i przechowuje klucze obce do obu stron.
CREATE TABLE students (
id SERIAL PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
enrolled_on DATE DEFAULT CURRENT_DATE
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
credits SMALLINT NOT NULL DEFAULT 3,
department VARCHAR(100)
);
CREATE TABLE enrollments (
student_id INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Dlaczego tabela łącząca?
- Ten złożony klucz główny (
student_id, course_id) pozwala na jeden wiersz na parę student–kurs. Jeśli wymagane jest powtarzanie zapisów w różnych semestrach, uwzględnij semestr w kluczu biznesowym lub wymodeluj każdy zapis oddzielnie. - Możesz dołączyć dodatkowe atrybuty do samej relacji, takie jak
gradelubenrolled_at, które nie należą wyłącznie do żadnej z encji. ON DELETE CASCADEna obu kluczach obcych oznacza, że usunięcie studenta lub kursu automatycznie czyści wiersze zapisów.
Zapytania o Dane wiele-do-wielu
-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;
-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';Przykłady relacji wiele-do-wielu ze świata rzeczywistego obejmują użytkownicy ↔ role, produkty ↔ tagi, aktorzy ↔ filmy, oraz zamówienia ↔ produkty (poprzez order_items tabela łącząca, która przechowuje również ilość i cenę).
Relacje jeden do jednego
A jeden do jednego Relacja (1:1) oznacza, że każdy wiersz w Tabeli A mapuje się na co najwyżej jeden wiersz w Tabeli B i odwrotnie. Ten wzorzec jest rzadszy, ale przydatny do dzielenia rzadko używanych lub wrażliwych kolumn na oddzielną tabelę.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(60) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
bio TEXT,
avatar_url VARCHAR(500),
birthdate DATE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Zauważ, że user_profiles.user_id jest zarówno klucz główny i klucz obcy. To ograniczenie wymusza mapowanie jeden-do-jednego na poziomie bazy danych — każdy użytkownik może mieć co najwyżej jeden wiersz profilu.
Jak relacje pojawiają się w ERD
Diagram związków encji wykorzystuje linie i symbole między tabelami, aby na pierwszy rzut oka przekazać liczność. Dwie najpopularniejsze notacje to Kurza łapka i Chen notacja.
| Relacja | Symbol kurzej łapki | Jak czytać |
|---|---|---|
| Jeden do jednego | ||—|| | Dokładnie jeden po każdej stronie |
| Jeden do wielu | ||—< | Jeden po lewej, wiele po prawej (zakończenie typu kurza stopka) |
| Wiele do wielu | >—< | Wiele po obu stronach; zaimplementowane poprzez tabelę łączącą |
Te symbole tekstowe są skrótem dla maksymalnej krotności. Opcjonalność wymaga dodatkowego znacznika zero lub jeden; sam klucz obcy nie wymaga, aby rodzic posiadał jakiekolwiek dzieci.
Podczas modelowania relacji wiele-do-wielu w narzędziu ERD, zazwyczaj widać trzy pola — dwie tabele encji i tabelę łączącą między nimi, z których każda jest połączona linią jeden-do-wielu.
Wklej dowolny z przykładów SQL z tego artykułu do Edytor Erd Go aby zobaczyć diagram wygenerowany automatycznie, wraz ze strzałkami kluczy obcych i wskaźnikami liczności.
Wybór właściwego wzorca relacji
Wybór niewłaściwej liczności prowadzi do duplikacji danych, złożonych zapytań i błędów integralności. Skorzystaj z tego schematu decyzyjnego:
- Czy jeden wiersz po stronie A może odnosić się do wielu wierszy po stronie B, ale nie odwrotnie? → Jeden do wielu. Umieść klucz obcy po stronie B.
- Czy wiersze po obu stronach mogą odnosić się do wielu wierszy po drugiej stronie? → Wiele do wielu. Utwórz tabelę łączącą z kluczem złożonym.
- Czy mapowanie jest ściśle 1:1 i potrzebujesz separacji tabel? → Jeden do jednego. Użyj tego samego klucza podstawowego jako klucza obcego.
W razie wątpliwości najpierw naszkicuj swój schemat. Szybki ERD ujawnia problemy z relacjami znacznie wcześniej niż napotkanie błędów ograniczeń na produkcji. Spróbuj zbudować swój diagram w Edytor Erd Go—zajmuje to sekundy i może zaoszczędzić godziny debugowania.



