Corso MariaDB / Progettare relazioni uno a molti, molti a molti e chiavi esterne

Progettare le relazioni definendo che cosa rappresenta ogni riga e collegando chiavi primarie e chiavi esterne. Un cliente può avere più ordini; la relazione molti a molti tra ordini e prodotti si rappresenta con righe ordine che conservano quantità e prezzo unitario al momento dell'acquisto.

Definire il significato delle righe e la direzione delle relazioni

Una chiave primaria identifica una riga; una chiave esterna, o foreign key, impone vincoli sui riferimenti a righe della tabella collegata. Mettere nome del cliente ed elenco dei prodotti in un'unica stringa d'ordine rende i calcoli delle quantità dipendenti dall'analisi del testo e ripete informazioni da aggiornare. Separare clienti, prodotti, ordini e righe secondo i fatti e le regole di manutenzione da conservare.

Tabella di esercizio Una riga rappresenta Identificazione o relazione
c25_customers Un cliente Chiave primaria customer_id
c25_products Un tipo di prodotto Chiave primaria product_id
c25_orders Un ordine customer_id riferito al cliente
c25_items Un prodotto in un ordine Chiave primaria composta order_id + product_id

Ogni riga conserva il prezzo unitario di acquisto in unit_price. L'importo di un vecchio ordine resta riproducibile dopo una variazione del prezzo di catalogo. Se un prodotto può comparire più volte nello stesso ordine con opzioni diverse, valutare un identificatore di riga o una chiave delle opzioni. La chiave composta semplificata permette soltanto una riga per coppia ordine-prodotto.

Un cliente può avere più ordini; ogni riga collega ordine e prodotto e conserva quantità e prezzo unitario di acquisto

Lo schema permette clienti senza ordini e prodotti ancora invenduti. Ogni ordine richiede un cliente e ogni riga richiede un ordine e un prodotto. La chiave esterna da sola non impone che un ordine contenga almeno una riga: questo requisito minimo va gestito separatamente. Il riferimento delle chiavi esterne MariaDB spiega i controlli di integrità referenziale.

Preparare l'esempio completo con chiavi esterne

Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega il salvataggio del codice e l'esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Prima di creare c25_customers, c25_products, c25_orders e c25_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se i nomi esistono già, esaminarne prima definizione e dati. Usare soltanto oggetti di esercitazione eliminabili e conservare le tabelle applicative. Verificare che @@SESSION.foreign_key_checks e @@SESSION.check_constraint_checks siano attivi perché la prova verifichi effettivamente i vincoli.

Creare e popolare prima clienti e prodotti, poi ordini e righe che li referenziano. La guida alle chiavi primarie e composte spiega gli identificatori utilizzati.

USE shopcourse;
CREATE TABLE c25_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
  REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
  REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
  REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

SQL conserva nomi e città coreani per mantenere coerenti i dati UTF-8 originali. I prodotti da 1 a 4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 hanno il valore di città Seoul, il cliente 2 Busan e il cliente 4 Daejeon. Conservare le stringhe esattamente come mostrate e confrontare i risultati tramite gli ID. Tutte le righe sono dati di esercizio.

Verificare relazioni uno a molti e molti a molti

SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;

Il risultato mostra quattro clienti, quattro ordini e sei righe ordine. Il cliente 1 possiede gli ordini 101 e 102. L'ordine 101 contiene i prodotti 1 e 2, mentre il prodotto 1 compare negli ordini 101 e 103. Ogni coppia ordine-prodotto è una riga con quantità e prezzo unitario storico.

La query JOIN confronta customer_id per mostrare la relazione e restituisce soltanto clienti con ordini corrispondenti. L'assenza del cliente 4 dal risultato non significa che la riga del cliente sia stata eliminata. Usare identificatori per le relazioni perché i nomi possono essere uguali e possono cambiare.

Provare riferimenti inesistenti ed eliminazioni vietate

Dopo la preparazione riuscita, eseguire separatamente i due errori intenzionali seguenti. Il primo si riferisce al cliente 999, che non esiste. Il secondo tenta di eliminare il cliente 1 mentre esistono ancora ordini che lo referenziano.

INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');

DELETE FROM c25_customers WHERE customer_id=1;

Entrambi devono essere rifiutati dai controlli delle chiavi esterne. Dopo ogni errore, verificare che il cliente 1 esista ancora e che gli ordini siano sempre quattro. Tenere gli errori intenzionali fuori dal normale file di preparazione perché possono interrompere le istruzioni successive. Mantenere foreign_key_checks attivo durante la verifica degli input.

In un altro progetto, una chiave esterna nullable può rappresentare l'assenza di una riga padre. customer_id negli ordini di questo esercizio è NOT NULL e rifiuta tale stato. La guida ai vincoli di input distingue la presenza del valore dagli altri requisiti.

Scegliere consapevolmente la politica di eliminazione

CASCADE può propagare l'eliminazione di una riga padre alle righe figlie. La comodità da sola non basta per sceglierlo: l'applicazione può richiedere di conservare, anonimizzare o eliminare dati diversi. Le righe ordine possono servire anche per resi e cronologia delle transazioni. RESTRICT impedisce qui di eliminare un cliente finché rimane referenziato da ordini.

Controllare compatibilità dei tipi delle chiavi, dimensione e segno degli interi, indici e motore di memorizzazione. SHOW CREATE TABLE e input intenzionalmente non validi dimostrano più della semplice presenza della parola REFERENCES in uno script. Le chiavi esterne verificano i riferimenti, ma non ogni regola aziendale, come la sufficienza delle scorte.

Esaminare le relazioni facoltative ed eliminare prima le tabelle figlie

Definire quali righe possano esistere prima della relazione: clienti senza ordini, prodotti ancora invenduti e ordini che richiedono un cliente. Distinguere gli attributi attuali del prodotto dai fatti dell'acquisto. Quando cambia il prezzo di catalogo, unit_price delle vecchie righe deve conservare il prezzo di acquisto se occorre riprodurre l'importo originale.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE per tutte e quattro le tabelle e righe memorizzate. Salvare ciò che va conservato. Eliminare soltanto gli oggetti di questo esercizio, iniziando dalle tabelle figlie e poi dalle tabelle padre referenziate. DROP TABLE rimuove definizione e dati e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;