InnoMarts logo
InnoMarts
Fast websites
← Back to course

Relacje: Primary Keys i Foreign Keys

Relacje: Primary Keys i Foreign Keys

Witaj z powrotem.

W poprzedniej lekcji nauczyłeś się filtrować i sortować dane.

Używałeś:

Bardzo użyteczne.

Bardzo SQL.

Dzisiaj przechodzimy do jednej z najważniejszych idei w relacyjnych bazach danych:

Relacje.

PostgreSQL jest relacyjną bazą danych.

To znaczy, że tabele mogą być ze sobą połączone.

Tabela nie musi przechowywać wszystkiego sama jak samotny arkusz Excel w ciemnym folderze.

Zamiast tego możemy podzielić dane na czyste tabele i połączyć je za pomocą kluczy.

Tutaj PostgreSQL zaczyna być poważny.

Nadal przyjazny.

Ale poważny.

Czego Się Nauczysz

W tej lekcji nauczysz się:

Na końcu tej lekcji zrozumiesz, jak tabele mogą pracować razem.

To duży krok.

Jedna tabela jest użyteczna.

Połączone tabele są potężne.

Jak narzędzia w warsztacie.

Jeden śrubokręt jest fajny.

Cała skrzynka narzędziowa jest lepsza.

Chyba że spadnie ci na stopę.

Problem z Jedną Wielką Tabelą

Wyobraź sobie, że chcemy przechowywać produkty i kategorie.

Moglibyśmy stworzyć jedną tabelę tak:

id | product_name | category_name
---|--------------|---------------
1  | Laptop       | Electronics
2  | Mouse        | Electronics
3  | Desk Chair   | Furniture
4  | Bookshelf    | Furniture

To działa.

Na początku.

Ale mamy duplikację.

Słowo Electronics pojawia się znowu i znowu.

Słowo Furniture też pojawia się znowu i znowu.

Teraz wyobraź sobie 10 000 produktów.

Jeśli musisz zmienić nazwę Electronics na Electronic Devices, musisz zaktualizować wiele wierszy.

To irytujące.

I niebezpieczne.

Bo jeden wiersz może stać się:

Electronic Devices

Inny może zostać:

Electronics

A jeszcze inny może zostać:

Eletronics

Gratulacje.

Masz teraz trzy kategorie.

I jedną ranę ortograficzną.

Lepsza Struktura

Zamiast zapisywać nazwy kategorii wielokrotnie, możemy stworzyć dwie tabele:

categories
products

Tabela categories przechowuje kategorie tylko raz.

Tabela products przechowuje produkty i odwołuje się do kategorii.

Przykład:

categories

id | name
---|-------------
1  | Electronics
2  | Furniture

products

id | name       | category_id
---|------------|------------
1  | Laptop     | 1
2  | Mouse      | 1
3  | Desk Chair | 2
4  | Bookshelf  | 2

Teraz produkty nie przechowują bezpośrednio nazwy kategorii.

Przechowują category_id.

To łączy każdy produkt z kategorią.

Czyściej.

Bezpieczniej.

Mniej powtarzania.

Mniej chaosu.

Baza danych oddycha lepiej.

Prawdopodobnie.

Primary Key

Primary key jednoznacznie identyfikuje każdy wiersz w tabeli.

Przykład:

id SERIAL PRIMARY KEY

W tej tabeli:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Kolumna id jest primary key.

To oznacza, że każda kategoria ma unikalne ID.

Przykład:

id | name
---|-------------
1  | Electronics
2  | Furniture

Primary key pomaga PostgreSQL jasno zidentyfikować każdy wiersz.

Bez zamieszania.

Bez pytania “które Electronics masz na myśli?”

Po prostu:

category id 1

Bardzo bezpośrednio.

Bardzo bazodanowo.

Foreign Key

Foreign key to kolumna, która wskazuje na primary key w innej tabeli.

Przykład:

category_id INTEGER REFERENCES categories(id)

To oznacza:

category_id musi odwoływać się do istniejącego id w tabeli categories.

Przykład:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category_id INTEGER REFERENCES categories(id)
);

Tutaj:

To tworzy relację.

PostgreSQL teraz wie:

Każdy produkt może należeć do kategorii.

Bardzo użyteczne.

Tabele zaczynają ze sobą rozmawiać.

Grzecznie.

Na razie.

Relacja One-to-Many

Relacja one-to-many oznacza:

Jeden wiersz w jednej tabeli może być połączony z wieloma wierszami w innej tabeli.

Przykład:

Jedna kategoria może mieć wiele produktów.

Electronics może mieć:

Furniture może mieć:

Czyli:

categories -> products
jedna kategoria -> wiele produktów

To jedna z najczęstszych relacji w bazach danych.

Inne przykłady:

jeden customer -> wiele orders
jeden author   -> wiele books
jeden teacher  -> wiele courses
jeden user     -> wiele posts

Jeśli budujesz prawdziwe aplikacje, zobaczysz relacje one-to-many wszędzie.

Są jak śrubki w meblach.

Małe.

Ważne.

Często ignorowane, dopóki coś się nie złamie.

Przygotuj Bazę Danych

Otwórz PostgreSQL:

sudo -iu postgres psql

Połącz się z bazą danych:

\c learning_postgresql

Jeśli nie masz tej bazy danych, utwórz ją:

CREATE DATABASE learning_postgresql;

Potem połącz się:

\c learning_postgresql

Teraz usuń stare tabele, jeśli istnieją.

Ważne:

Najpierw usuń products, bo będzie zależeć od categories.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Kolejność ma znaczenie.

Jeśli jedna tabela zależy od drugiej, PostgreSQL może nie pozwolić usunąć tabeli parent jako pierwszej.

PostgreSQL chroni relacje.

Jak surowy rodzic.

Ale dla danych.

Stwórz Tabelę Categories

Stwórz tabelę categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Ta tabela ma:

id jest primary key.

name jest wymagane i unikalne.

To oznacza, że nie możemy mieć dwóch kategorii z tą samą nazwą.

Dobrze.

Żadnego podwójnego Electronics.

Żadnej imprezy z Electronics, electronics i Electronicss.

No dobrze, PostgreSQL nadal może traktować różną pisownię jako różne wartości.

Ale UNIQUE chroni przed dokładnymi duplikatami.

Baza danych jest silna.

Nie magiczna.

Wstaw Kategorie

Wstaw kategorie:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Sprawdź tabelę:

SELECT * FROM categories;

Powinieneś zobaczyć:

1 | Electronics
2 | Furniture
3 | Office

ID mogą być inne, jeśli wcześniej już wstawiałeś dane.

To normalne.

Nie panikuj.

PostgreSQL liczy.

Czasami pamięta stare numery nawet po usunięciach.

Bazy danych pamiętają więcej, niż ludzie oczekują.

Lekko straszne.

Stwórz Tabelę Products

Teraz stwórz tabelę products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Ta tabela ma:

Najważniejsza część to:

category_id INTEGER REFERENCES categories(id)

To tworzy foreign key.

Oznacza, że każde category_id w products musi pasować do istniejącego id w categories.

PostgreSQL będzie pilnować tej reguły.

Bo PostgreSQL nie jest tutaj dla wymyślonych kategorii.

Wstaw Products z Category IDs

Wstaw produkty:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Teraz sprawdź:

SELECT * FROM products;

Powinieneś zobaczyć produkty z category_id.

Przykład:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Desk Chair | 150.00 | 2
4  | Notebook   | 5.00   | 3
5  | Pen        | 2.00   | 3

To jest dobre.

Ale jeszcze niezbyt czytelne.

Widzimy category_id.

Nie widzimy nazw kategorii.

Wkrótce nauczymy się JOIN.

Na razie zrozum relację.

Produkt wie, do której kategorii należy.

Przez ID.

Bardzo relacyjne.

Bardzo PostgreSQL.

Spróbuj Złej Foreign Key

Teraz spróbuj wstawić produkt z kategorią, która nie istnieje:

INSERT INTO products (name, price, category_id)
VALUES ('Mystery Device', 99.00, 999);

PostgreSQL powinien to odrzucić.

Dlaczego?

Bo nie ma kategorii z id = 999.

To foreign key wykonuje swoją pracę.

Bez foreign key PostgreSQL zaakceptowałby ten wiersz.

Potem miałbyś produkt wskazujący na nic.

Kategorię ducha.

Smutny mały osierocony wiersz.

Foreign keys temu zapobiegają.

Chronią relacje.

Mówią:

Nie możesz wskazywać na coś, co nie istnieje.

Bardzo surowo.

Bardzo użytecznie.

Dlaczego Foreign Keys Są Ważne

Foreign keys chronią twoje dane.

Zapobiegają takim sytuacjom jak:

Bez foreign keys baza danych może stać się niespójna.

Przykład:

Product mówi category_id = 999
Ale category 999 nie istnieje

To źle.

Aplikacja może się zepsuć.

Raport może być błędny.

Developer może zacząć pić za dużo kawy.

Foreign keys zmniejszają ten typ bólu.

Nie cały ból.

Ale część.

I to się liczy.

Tabele Parent i Child

W relacjach często mówimy:

parent table
child table

W naszym przykładzie:

categories = parent table
products   = child table

Dlaczego?

Bo products zależy od categories.

Produkt może odwoływać się do kategorii.

Czyli:

categories.id

jest kluczem parent.

A:

products.category_id

jest foreign key.

Inny przykład:

customers = parent table
orders    = child table

Jeden customer może mieć wiele orders.

Każdy order odwołuje się do jednego customer.

To słownictwo pojawia się często.

Nie bój się go.

Parent table.

Child table.

Rodzina bazodanowa.

Mniej emocjonalna niż prawdziwa rodzina.

Zwykle.

Sprawdź Tabele

Użyj:

\d categories

Potem:

\d products

W tabeli products PostgreSQL powinien pokazać constraint foreign key.

Możesz zobaczyć coś takiego:

Foreign-key constraints:
  "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

Nazwa może być wygenerowana automatycznie.

Może wyglądać brzydko.

To normalne.

PostgreSQL nazywa constraints jak robot nazywający dzieci.

Funkcjonalnie.

Nie poetycko.

Problem z DELETE: Parent Row Jest Używany

Spróbuj usunąć kategorię, która ma produkty:

DELETE FROM categories
WHERE id = 1;

PostgreSQL może to odrzucić.

Dlaczego?

Bo produkty nadal odwołują się do kategorii 1.

Gdyby PostgreSQL pozwolił na usunięcie, produkty wskazywałyby na brakującą kategorię.

Znowu osierocone wiersze.

PostgreSQL mówi:

Nie. Najpierw napraw dzieci.

Bardzo rodzicielsko.

Bardzo bazodanowo.

Aby usunąć kategorię, najpierw musiałbyś zaktualizować albo usunąć powiązane produkty.

Przykład:

DELETE FROM products
WHERE category_id = 1;

Potem:

DELETE FROM categories
WHERE id = 1;

Uważaj z delete.

Zawsze używaj najpierw SELECT.

Znasz zasadę.

Kask na głowę.

Zachowanie ON DELETE

Foreign keys mogą definiować, co stanie się, gdy parent row zostanie usunięty.

Popularne opcje:

Domyślnie PostgreSQL zwykle blokuje usunięcie parent rows, które nadal są referencjonowane.

To bezpieczne.

ON DELETE CASCADE oznacza:

Jeśli parent zostanie usunięty, usuń też powiązane child rows.

Przykład:

category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE

Uważaj.

CASCADE jest potężne.

Czasami użyteczne.

Czasami niebezpieczne.

Może automatycznie usunąć wiele powiązanych wierszy.

Jak domino.

Ale z danymi.

W projektach dla początkujących używaj tego tylko wtedy, gdy naprawdę rozumiesz konsekwencje.

Baza danych nie zapyta:

Czy jesteś emocjonalnie pewien?

Ona po prostu to zrobi.

Unikaj Powtarzanych Danych

Relacje pomagają unikać duplikacji danych.

Zły design:

products

id | name   | category_name
---|--------|--------------
1  | Laptop | Electronics
2  | Mouse  | Electronics

Lepszy design:

categories

id | name
---|-------------
1  | Electronics

products

id | name   | category_id
---|--------|------------
1  | Laptop | 1
2  | Mouse  | 1

Teraz nazwa kategorii istnieje w jednym miejscu.

Czyściej.

Jeśli nazwa kategorii się zmieni, aktualizujesz jeden wiersz.

Nie 500 wierszy.

To jeden z powodów, dla których istnieją relacyjne bazy danych.

Mniej duplikacji.

Lepsza spójność.

Mniej katastrof ortograficznych.

Inny Przykład: Students i Courses

Wyobraź sobie platformę edukacyjną.

Jeden kurs może mieć wielu students.

Jeden student może też dołączyć do wielu courses.

To jest relacja many-to-many.

Relacje many-to-many potrzebują dodatkowej tabeli.

Przykład:

students
courses
enrollments

Tabela enrollments łączy students i courses.

Nauczymy się tego dokładniej później.

Na razie zapamiętaj:

Nie panikuj.

To normalne.

Bazy danych lubią strukturę.

Czasami dużo struktury.

Jak bardzo zorganizowana bibliotekarka.

Prosty Przykład One-to-Many: Authors i Books

Stwórzmy jeszcze jedną prostą relację.

Jeden author może mieć wiele books.

Najpierw usuń stare tabele, jeśli trzeba:

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

Stwórz authors:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Stwórz books:

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  author_id INTEGER REFERENCES authors(id)
);

Wstaw authors:

INSERT INTO authors (name)
VALUES
  ('George Orwell'),
  ('Jane Austen');

Wstaw books:

INSERT INTO books (title, author_id)
VALUES
  ('1984', 1),
  ('Animal Farm', 1),
  ('Pride and Prejudice', 2);

Sprawdź:

SELECT * FROM authors;
SELECT * FROM books;

Teraz jeden author może mieć wiele books.

To jest relacja one-to-many.

Prosta.

Potężna.

Klasyczna.

Jak herbata.

Ale SQL.

Typowe Błędy

Tworzenie Najpierw Tabeli Child

Zła kolejność:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id)
);

Jeśli categories jeszcze nie istnieje, PostgreSQL nie może stworzyć foreign key.

Najpierw stwórz tabelę parent.

Potem tabelę child.

Poprawna kolejność:

1. categories
2. products

Najpierw rodzice.

Potem dzieci.

Planowanie rodziny bazodanowej.

Wstawianie Child Rows Przed Parent Rows

Źle:

INSERT INTO products (name, price, category_id)
VALUES ('Laptop', 900.00, 1);

Jeśli kategoria 1 nie istnieje, PostgreSQL odrzuci to.

Poprawnie:

1. Wstaw categories
2. Wstaw products

Wskazywany wiersz musi istnieć najpierw.

Nie możesz wskazać krzesła, którego nie ma.

Chyba że robisz filozofię.

To jest SQL.

Używanie Nazw Zamiast ID

Zły pomysł:

product przechowuje category_name bezpośrednio

Lepiej:

product przechowuje category_id

Nazwy mogą się zmieniać.

ID powinny pozostać stabilne.

Nazwa kategorii może zmienić się z Office na Office Supplies.

Ale jej ID może zostać takie samo.

ID są nudne.

Dlatego są użyteczne.

Ignorowanie Foreign Keys

Możesz budować tabele bez foreign keys.

Ale wtedy PostgreSQL nie może chronić relacji.

To oznacza, że aplikacja musi robić całą pracę.

A aplikacje piszą ludzie.

Ludzie zapominają rzeczy.

Foreign keys to pasy bezpieczeństwa bazy danych.

Używaj ich.

Praktyka

Stwórz dwie tabele:

customers
orders

Jeden customer może mieć wiele orders.

Stwórz customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL
);

Stwórz orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Wstaw customers:

INSERT INTO customers (name, email)
VALUES
  ('Anna', 'anna@example.com'),
  ('Marco', 'marco@example.com');

Wstaw orders:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Sprawdź:

SELECT * FROM customers;
SELECT * FROM orders;

Potem spróbuj wstawić order z brakującym customer:

INSERT INTO orders (total, customer_id)
VALUES (99.00, 999);

PostgreSQL powinien to odrzucić.

Dobrze.

To foreign key chroni twoje dane.

Mini Wyzwanie

Stwórz małą strukturę bazy danych dla blog posts.

Potrzebujesz dwóch tabel:

authors
posts

Reguły:

Sugerowana struktura:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  author_id INTEGER REFERENCES authors(id)
);

Wstaw przynajmniej dwóch authors.

Wstaw przynajmniej trzy posts.

Potem uruchom:

SELECT * FROM authors;
SELECT * FROM posts;

Spróbuj wstawić post z author_id = 999.

PostgreSQL powinien to odrzucić.

To nie PostgreSQL jest irytujący.

To PostgreSQL pilnuje twoich danych jak poważna bibliotekarka z klawiaturą.

Podsumowanie

Dzisiaj nauczyłeś się:

To ogromny krok.

Przechodzisz teraz od izolowanych tabel do projektowania relacyjnych baz danych.

Właśnie po to istnieje PostgreSQL.

Tabele nie są samotnymi wyspami.

Mogą się łączyć.

Mogą się referencjonować.

Mogą się wzajemnie chronić.

Bardzo piękne.

W sposób bazodanowy.

Następna Lekcja

W następnej lekcji nauczymy się JOIN.

To tutaj relacje stają się widoczne w wynikach zapytań.

Teraz products pokazują category_id.

Użyteczne, ale niezbyt przyjazne.

Z JOIN pokażemy:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

To tam połączone tabele zaczynają wyglądać potężnie.

I dużo bardziej czytelnie.