InnoMarts logo
InnoMarts
Fast websites
← Back to course

Relazioni: Primary Keys e Foreign Keys

Relazioni: Primary Keys e Foreign Keys

Bentornato.

Nella lezione precedente hai imparato a filtrare e ordinare dati.

Hai usato:

Molto utile.

Molto SQL.

Oggi passiamo a una delle idee più importanti nei database relazionali:

Le relazioni.

PostgreSQL è un database relazionale.

Questo significa che le tabelle possono essere collegate.

Una tabella non deve salvare tutto da sola come un foglio Excel solitario in una cartella buia.

Invece possiamo dividere i dati in tabelle pulite e collegarle usando chiavi.

Qui PostgreSQL diventa serio.

Ancora amichevole.

Ma serio.

Cosa Imparerai

In questa lezione imparerai:

Alla fine di questa lezione capirai come le tabelle possono lavorare insieme.

Questo è un grande passo.

Una tabella è utile.

Tabelle collegate sono potenti.

Come strumenti in un laboratorio.

Un cacciavite è comodo.

Una cassetta completa è meglio.

A meno che non ti cada sul piede.

Il Problema di una Tabella Gigante

Immagina di voler salvare prodotti e categorie.

Potremmo creare una sola tabella così:

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

Funziona.

All’inizio.

Ma c’è duplicazione.

La parola Electronics appare più volte.

La parola Furniture appare più volte.

Ora immagina 10.000 prodotti.

Se devi rinominare Electronics in Electronic Devices, devi aggiornare tante righe.

È fastidioso.

Ed è pericoloso.

Perché una riga può diventare:

Electronic Devices

Un’altra può restare:

Electronics

Un’altra può diventare:

Eletronics

Complimenti.

Ora hai tre categorie.

E una ferita grammaticale.

Una Struttura Migliore

Invece di salvare i nomi delle categorie ripetutamente, possiamo creare due tabelle:

categories
products

La tabella categories salva le categorie una volta sola.

La tabella products salva i prodotti e fa riferimento a una categoria.

Esempio:

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

Ora i prodotti non salvano direttamente il nome della categoria.

Salvano category_id.

Questo collega ogni prodotto a una categoria.

Più pulito.

Più sicuro.

Meno ripetizione.

Meno caos.

Il database respira meglio.

Probabilmente.

Primary Key

Una primary key identifica in modo unico ogni riga in una tabella.

Esempio:

id SERIAL PRIMARY KEY

In questa tabella:

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

La colonna id è la primary key.

Questo significa che ogni categoria ha un ID unico.

Esempio:

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

La primary key aiuta PostgreSQL a identificare ogni riga chiaramente.

Nessuna confusione.

Niente “quale Electronics intendi?”

Solo:

category id 1

Molto diretto.

Molto database.

Foreign Key

Una foreign key è una colonna che punta a una primary key in un’altra tabella.

Esempio:

category_id INTEGER REFERENCES categories(id)

Questo significa:

category_id deve riferirsi a un id esistente nella tabella categories.

Esempio:

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

Qui:

Questo crea una relazione.

PostgreSQL ora sa:

Ogni prodotto può appartenere a una categoria.

Molto utile.

Le tabelle ora parlano.

Educatamente.

Per ora.

Relazione One-to-Many

Una relazione one-to-many significa:

Una riga in una tabella può essere collegata a molte righe in un’altra tabella.

Esempio:

Una categoria può avere molti prodotti.

Electronics può avere:

Furniture può avere:

Quindi:

categories -> products
una categoria -> molti prodotti

Questa è una delle relazioni più comuni nei database.

Altri esempi:

un customer -> molti orders
un author   -> molti books
un teacher  -> molti courses
un user     -> molti posts

Se costruisci applicazioni reali, vedrai relazioni one-to-many ovunque.

Sono come le viti nei mobili.

Piccole.

Importanti.

Spesso ignorate finché qualcosa si rompe.

Prepara il Database

Apri PostgreSQL:

sudo -iu postgres psql

Connettiti al database:

\c learning_postgresql

Se non hai questo database, crealo:

CREATE DATABASE learning_postgresql;

Poi connettiti:

\c learning_postgresql

Ora rimuovi vecchie tabelle se esistono.

Importante:

Elimina prima products perché dipenderà da categories.

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

L’ordine conta.

Se una tabella dipende da un’altra, PostgreSQL potrebbe non permetterti di eliminare prima la tabella parent.

PostgreSQL protegge le relazioni.

Come un genitore severo.

Ma per i dati.

Creare la Tabella Categories

Crea la tabella categories:

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

Questa tabella ha:

id è la primary key.

name è obbligatorio e unico.

Questo significa che non possiamo avere due categorie con lo stesso nome.

Bene.

Niente doppio Electronics.

Niente festa con Electronics, electronics ed Electronicss.

Beh, PostgreSQL può comunque trattare spelling diversi come valori diversi.

Ma UNIQUE protegge i duplicati esatti.

Il database è forte.

Non magico.

Inserire Categorie

Inserisci categorie:

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

Controlla la tabella:

SELECT * FROM categories;

Dovresti vedere:

1 | Electronics
2 | Furniture
3 | Office

Gli ID potrebbero essere diversi se avevi già inserito dati prima.

Va bene.

Non andare nel panico.

PostgreSQL conta.

A volte ricorda vecchi numeri anche dopo i delete.

I database ricordano più di quanto gli umani si aspettino.

Leggermente inquietante.

Creare la Tabella Products

Ora crea la tabella 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)
);

Questa tabella ha:

La parte importante è:

category_id INTEGER REFERENCES categories(id)

Questo crea la foreign key.

Significa che ogni category_id in products deve corrispondere a un id esistente in categories.

PostgreSQL proteggerà questa regola.

Perché PostgreSQL non è qui per categorie immaginarie.

Inserire Products con Category IDs

Inserisci prodotti:

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

Ora controlla:

SELECT * FROM products;

Dovresti vedere prodotti con category_id.

Esempio:

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

Questo va bene.

Ma non è ancora molto leggibile.

Vediamo category_id.

Non vediamo i nomi delle categorie.

Presto impareremo JOIN.

Per ora capisci la relazione.

Il prodotto sa a quale categoria appartiene.

Attraverso l’ID.

Molto relazionale.

Molto PostgreSQL.

Prova una Foreign Key Sbagliata

Ora prova a inserire un prodotto con una categoria che non esiste:

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

PostgreSQL dovrebbe rifiutarlo.

Perché?

Perché non esiste una categoria con id = 999.

Questa è la foreign key che fa il suo lavoro.

Senza foreign key, PostgreSQL accetterebbe la riga.

Poi avresti un prodotto che punta al nulla.

Una categoria fantasma.

Una piccola riga orfana e triste.

Le foreign keys impediscono questo.

Proteggono le relazioni.

Dicono:

Non puoi puntare a qualcosa che non esiste.

Molto severo.

Molto utile.

Perché le Foreign Keys Contano

Le foreign keys proteggono i dati.

Impediscono:

Senza foreign keys, il database può diventare inconsistente.

Esempio:

Product dice category_id = 999
Ma category 999 non esiste

Questo è male.

L’applicazione può rompersi.

Il report può essere sbagliato.

Lo sviluppatore può iniziare a bere troppo caffè.

Le foreign keys riducono questo tipo di dolore.

Non tutto il dolore.

Ma una parte.

E conta.

Tabelle Parent e Child

In una relazione, spesso diciamo:

parent table
child table

Nel nostro esempio:

categories = parent table
products   = child table

Perché?

Perché products dipende da categories.

Un prodotto può riferirsi a una categoria.

Quindi:

categories.id

è la chiave parent.

E:

products.category_id

è la foreign key.

Altro esempio:

customers = parent table
orders    = child table

Un customer può avere molti orders.

Ogni order si riferisce a un customer.

Questo vocabolario appare spesso.

Non temerlo.

Parent table.

Child table.

Famiglia database.

Meno emotiva della famiglia reale.

Di solito.

Ispezionare le Tabelle

Usa:

\d categories

Poi:

\d products

Nella tabella products, PostgreSQL dovrebbe mostrare il vincolo foreign key.

Potresti vedere qualcosa tipo:

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

Il nome può essere generato automaticamente.

Può sembrare brutto.

È normale.

PostgreSQL nomina i constraint come un robot che dà nomi ai figli.

Funzionale.

Non poetico.

Problema con DELETE: Riga Parent in Uso

Prova a eliminare una categoria che ha prodotti:

DELETE FROM categories
WHERE id = 1;

PostgreSQL potrebbe rifiutarlo.

Perché?

Perché alcuni prodotti si riferiscono ancora alla categoria 1.

Se PostgreSQL permettesse l’eliminazione, i prodotti punterebbero a una categoria mancante.

Di nuovo, righe orfane.

PostgreSQL dice:

No. Sistema prima i figli.

Molto parentale.

Molto database.

Per eliminare la categoria, dovresti prima aggiornare o eliminare i prodotti collegati.

Esempio:

DELETE FROM products
WHERE category_id = 1;

Poi:

DELETE FROM categories
WHERE id = 1;

Fai attenzione con i delete.

Usa sempre SELECT prima.

Conosci la regola.

Casco in testa.

Comportamento ON DELETE

Le foreign keys possono definire cosa succede quando una riga parent viene eliminata.

Opzioni comuni:

Di default, PostgreSQL di solito impedisce di eliminare righe parent ancora referenziate.

È sicuro.

ON DELETE CASCADE significa:

Se il parent viene eliminato, elimina anche le righe child collegate.

Esempio:

category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE

Fai attenzione.

CASCADE è potente.

A volte utile.

A volte pericoloso.

Può eliminare molte righe collegate automaticamente.

Come domino.

Ma con i dati.

Nei progetti da principiante, usalo solo quando capisci davvero la conseguenza.

Il database non dirà:

Sei emotivamente sicuro?

Lo farà e basta.

Evitare Dati Ripetuti

Le relazioni aiutano a evitare dati duplicati.

Design cattivo:

products

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

Design migliore:

categories

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

products

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

Ora il nome della categoria esiste in un solo posto.

Più pulito.

Se il nome della categoria cambia, aggiorni una riga.

Non 500 righe.

Questo è uno dei motivi per cui esistono i database relazionali.

Meno duplicazione.

Migliore consistenza.

Meno disastri ortografici.

Altro Esempio: Students e Courses

Immagina una piattaforma di apprendimento.

Un corso può avere molti studenti.

Uno studente può anche partecipare a molti corsi.

Questa è una relazione many-to-many.

Le relazioni many-to-many hanno bisogno di una tabella extra.

Esempio:

students
courses
enrollments

La tabella enrollments collega students e courses.

Lo impareremo meglio più avanti.

Per ora ricorda:

Non andare nel panico.

È normale.

I database amano la struttura.

A volte molta struttura.

Come una bibliotecaria molto organizzata.

Esempio One-to-Many: Authors e Books

Creiamo un’altra relazione semplice.

Un author può avere molti books.

Prima rimuovi vecchie tabelle se servono:

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

Crea authors:

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

Crea books:

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

Inserisci authors:

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

Inserisci books:

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

Controlla:

SELECT * FROM authors;
SELECT * FROM books;

Ora un author può avere molti books.

Questa è una relazione one-to-many.

Semplice.

Potente.

Classica.

Come il tè.

Ma SQL.

Errori Comuni

Creare Prima la Tabella Child

Ordine sbagliato:

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

Se categories non esiste ancora, PostgreSQL non può creare la foreign key.

Crea prima la tabella parent.

Poi la tabella child.

Ordine corretto:

1. categories
2. products

Prima i genitori.

Poi i figli.

Pianificazione familiare del database.

Inserire Child Rows Prima dei Parent Rows

Sbagliato:

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

Se la categoria 1 non esiste, PostgreSQL rifiuta.

Corretto:

1. Inserisci categories
2. Inserisci products

La riga referenziata deve esistere prima.

Non puoi indicare una sedia che non c’è.

A meno che tu non stia facendo filosofia.

Questo è SQL.

Usare Nomi Invece di ID

Cattiva idea:

product salva category_name direttamente

Meglio:

product salva category_id

I nomi possono cambiare.

Gli ID dovrebbero restare stabili.

Una categoria può cambiare nome da Office a Office Supplies.

Ma il suo ID può restare lo stesso.

Gli ID sono noiosi.

Per questo sono utili.

Ignorare le Foreign Keys

Puoi creare tabelle senza foreign keys.

Ma allora PostgreSQL non può proteggere le relazioni.

Significa che l’applicazione deve fare tutto il lavoro.

E le applicazioni sono scritte da umani.

Gli umani dimenticano cose.

Le foreign keys sono cinture di sicurezza per il database.

Usale.

Pratica

Crea due tabelle:

customers
orders

Un customer può avere molti orders.

Crea customers:

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

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

Inserisci customers:

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

Inserisci orders:

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

Controlla:

SELECT * FROM customers;
SELECT * FROM orders;

Poi prova a inserire un order con un customer mancante:

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

PostgreSQL dovrebbe rifiutare.

Bene.

È la foreign key che protegge i dati.

Mini Challenge

Crea una piccola struttura database per blog posts.

Ti servono due tabelle:

authors
posts

Regole:

Struttura suggerita:

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

Inserisci almeno due authors.

Inserisci almeno tre posts.

Poi esegui:

SELECT * FROM authors;
SELECT * FROM posts;

Prova a inserire un post con author_id = 999.

PostgreSQL dovrebbe rifiutarlo.

Questo non è PostgreSQL che fa il fastidioso.

È PostgreSQL che protegge i tuoi dati come una bibliotecaria seria con una tastiera.

Riepilogo

Oggi hai imparato:

Questo è un passo enorme.

Ora stai passando da tabelle isolate al design di database relazionali.

È per questo che PostgreSQL esiste.

Le tabelle non sono isole solitarie.

Possono collegarsi.

Possono referenziarsi.

Possono proteggersi a vicenda.

Molto bello.

In modo database.

Prossima Lezione

Nella prossima lezione impareremo JOIN.

È qui che le relazioni diventano visibili nei risultati delle query.

Adesso i products mostrano category_id.

Utile, ma non molto amichevole.

Con JOIN, mostreremo:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

È lì che le tabelle collegate iniziano a sembrare potenti.

E molto più leggibili.