📊 Guida Completa SQL

Structured Query Language - Dal principiante all'esperto

🎯 Cos'è SQL?

SQL (Structured Query Language) è il linguaggio standard per interagire con i database relazionali. Permette di creare, modificare, interrogare, controllare e gestire i dati in modo efficiente e strutturato e può essere considerato come l'unione dei 5 sottolinguaggi: DDL, DML, DCL, TCL, QL.

DDL - Data Definition Language

Linguaggio per definire la struttura del database

  • CREATE - Crea oggetti
  • ALTER - Modifica strutture
  • DROP - Elimina oggetti
  • TRUNCATE - Svuota tabelle

DML - Data Manipulation Language

Linguaggio per manipolare i dati

  • INSERT - Inserisce righe
  • UPDATE - Modifica righe
  • DELETE - Elimina righe

DCL - Data Control Language

Linguaggio per gestire i permessi e la sicurezza

  • GRANT - Assegna privilegi
  • REVOKE - Revoca privilegi

TCL - Transaction Control Language

Linguaggio per gestire le transazioni

  • BEGIN / START TRANSACTION
  • COMMIT - Conferma
  • ROLLBACK - Annulla

QL - Query Language

Linguaggio per interrogare i dati con precisione

  • SELECT - Seleziona colonne
  • FROM - Specifica tabelle
  • WHERE - Filtra righe
  • GROUP BY - Raggruppa risultati
  • HAVING - Filtra gruppi
  • ORDER BY - Ordina risultati
  • LIMIT - Limita numero righe

📚 Approfondimento Separato

La Query Language (QL) è trattata in documenti HTML dedicati per consentire un apprendimento graduale, esempi contestualizzati e quiz interattivi focalizzati sulle interrogazioni avanzate.

🔧 DDL - Data Definition Language

DDL

CREATE DATABASE

Descrizione

L'istruzione CREATE DATABASE è il fondamento di ogni progetto database in MySQL e MariaDB. Questo comando crea un nuovo database, che funziona come contenitore principale per tutti gli oggetti del database quali tabelle, viste, stored procedure e trigger. Il database rappresenta il livello più alto di organizzazione dei dati e deve essere pianificato con attenzione prima di procedere con la creazione degli oggetti contenuti.

Quando crei un database, MySQL genera automaticamente una directory nel file system del server con il nome specificato. All'interno di questa directory verranno memorizzati tutti i file relativi alle tabelle e agli indici creati successivamente. È importante scegliere un nome significativo e conforme alle convenzioni di naming del tuo progetto, poiché modificare il nome di un database esistente richiede operazioni complesse che possono influenzare le applicazioni connesse.

La clausola opzionale IF NOT EXISTS è particolarmente utile negli script di installazione e nei processi di deployment automatizzati. Questa clausola previene la generazione di errori nel caso in cui il database esista già, permettendo allo script di continuare l'esecuzione senza interruzioni. Senza questa clausola, un tentativo di creare un database già esistente genererebbe un errore che arresterebbe l'esecuzione dello script.

Sintassi

CREATE DATABASE [IF NOT EXISTS] nome_database;

Esempi Pratici

CREATE DATABASE azienda;

Spiegazione: Questo comando crea un nuovo database denominato "azienda". Dopo l'esecuzione, il database sarà visibile nell'elenco dei database disponibili e potrà essere utilizzato per creare tabelle e altri oggetti. È la forma più semplice di creazione database, adatta quando si è sicuri che il database non esista già e non si hanno requisiti specifici per il character set.

CREATE DATABASE IF NOT EXISTS ecommerce;

Spiegazione: Questo comando tenta di creare il database "ecommerce" solo se non esiste già. La clausola IF NOT EXISTS previene la generazione di un errore se il database è già presente. Questa forma è particolarmente utile negli script di setup che potrebbero essere eseguiti più volte, come negli ambienti di sviluppo, nei container Docker, o nei processi di deployment automatizzato. Senza questa clausola, l'esecuzione dello script si arresterebbe con un errore di "database already exists".

CREATE DATABASE applicazione_web

CHARACTER SET utf8mb4

COLLATE utf8mb4_unicode_ci;

Spiegazione: Questo comando crea un database ottimizzato per memorizzare testo in più lingue, inclusi caratteri speciali e emoji. Il character set utf8mb4 è la codifica Unicode completa che supporta tutti i caratteri esistenti, inclusi quelli delle lingue asiatiche e i simboli emoji. La collazione utf8mb4_unicode_ci garantisce confronti case-insensitive (la "A" e la "a" sono considerate uguali) e ordinamenti linguistici corretti secondo le regole Unicode. Questa configurazione è lo standard consigliato per tutte le nuove applicazioni moderne.

Nota Operativa

Per iniziare a utilizzare il database appena creato, devi selezionarlo con l'istruzione USE nome_database. Questa selezione rimane attiva per tutta la sessione corrente del client MySQL. In molte applicazioni, la stringa di connessione specifica già il database da utilizzare, rendendo il comando USE necessario solo durante le operazioni manuali di amministrazione.

Best Practice
  • **Convenzioni di naming**: Utilizza nomi descrittivi in minuscolo con underscore per separare le parole (es. gestione_magazzino invece di GM o gestionemagazzino)
  • **Character set**: Definisci sempre il character set e la collazione a livello di database per garantire coerenza con le applicazioni che supportano Unicode
  • **Backup**: Dopo la creazione, configura immediatamente le procedure di backup per proteggere i dati
  • **Privilegi**: Assegna i privilegi appropriati agli utenti subito dopo la creazione per garantire la sicurezza dei dati
DDL

CREATE TABLE

Descrizione

Crea una nuova tabella nel database, definendo il nome delle colonne, i tipi di dati e i vincoli (constraints).

📊 Tabella Riassuntiva dei Tipi di Dati SQL (MySQL/MariaDB)

Quando crei una tabella, ogni colonna deve avere un tipo di dato. Questo dice al database che tipo di informazioni può contenere (numeri, testi, date...). Scegliere il tipo giusto è fondamentale per risparmiare spazio e velocizzare le query. Ecco la guida completa con esempi reali e spiegazioni approfondite.

🔢 Numeri Interi (senza virgola) e decimali
💰 Numeri con la Virgola (decimali)
TipoDimensioneIntervallo (con segno)Intervallo UNSIGNEDEsempio d'uso
TINYINT1 byte-128 a 1270 a 255Età, voto scolastico (0-10), booleano
SMALLINT2 byte-32.768 a 32.7670 a 65.535Quantità di prodotti in magazzino
INT4 byte-2 miliardi a +2 miliardi0 a 4 miliardiID utente, contatori normali
BIGINT8 byte-9,2×10¹⁸ a +9,2×10¹⁸0 a 1,8×10¹⁹Visualizzazioni pagina, ID per sistemi enormi
💡 Cosa significa UNSIGNED?
Di default i numeri interi possono essere positivi o negativi. Aggiungendo UNSIGNED dici al database: "questo numero sarà sempre ≥ 0". In questo modo raddoppi il massimo positivo (perché non usi spazio per il segno). È perfetto per ID, quantità, età, ecc.
Esempio: eta TINYINT UNSIGNED → può andare da 0 a 255, ideale per l'età.
📐 Come si legge DECIMAL(p,s)?
• p = precisione totale (cifre toatli prima + dopo la virgola)
• s = scala (cifre dopo la virgola)
Esempi reali:
→ DECIMAL(4,2) → 2 cifre dopo la virgola, totale 4 cifre → max 99,99 (utile per prezzi piccoli)
→ DECIMAL(2,1) → 1 cifra dopo la virgola, totale 2 cifre → max 9,9 (ottimo per i voti scolastici da 0 a 10, ad esempio 9.5)
→ DECIMAL(5,2) → 2 cifre dopo la virgola, totale 5 cifre → max 999,99 ideale per prezzi inferiori a 1000€
⚠️ Regola d'oro: per i soldi usa sempre DECIMAL! FLOAT e DOUBLE introducono errori di arrotondamento (es. 0.1 + 0.2 potrebbe dare 0.30000000000000004).
TipoPrecisioneA cosa serveEsempio
DECIMAL(p,s)Esatta (massimo 65 cifre totali)Soldi, prezzi, voti precisiDECIMAL(5,2) → max 999.99
FLOATApprossimata (~7 cifre)Misure scientifiche, coordinate3.1415927 (pi greco)
DOUBLEApprossimata (~15 cifre)Calcoli più precisi3.14159265358979
🔍 CHAR vs VARCHAR: un esempio concreto
Immagina di memorizzare un codice fiscale italiano: è sempre esattamente 16 caratteri. Se usi VARCHAR(16), ogni codice occuperà 16 caratteri + 1 byte per indicare il numero di caratteri . Con CHAR(16) occupa esattamente 16 byte (non è necessario il byte aggiuntivo per indicare il numero di cratteri) ed è più veloce per le ricerche perché il database sa che tutti i valori hanno la stessa lunghezza. Quindi:
→ CHAR: lunghezza fissa (es. codici, hash, flag).
→ VARCHAR: lunghezza variabile (es. nomi, indirizzi).
📅 Date e Orari – Formati e utilizzo
TipoLunghezza massimaCaratteristicheQuando usarlo
CHAR(n)fissa (max 255)Occupare sempre n caratteri (riempiendo con spazi)Codici a lunghezza fissa: codice fiscale (16 caratteri), targa (7 caratteri), sesso (1 carattere: 'M'/'F'), stato (2 caratteri: esempio IT). È più veloce di VARCHAR per ricerche esatte ma va usato solo per testi di cui si conosce il numero delle lettere altrimenti ad esempio se uso char(10) per un campo ed inserisco nel campo una parola di 2 lettere, avrò comunque occupato lo spazio di 10 lettere .
VARCHAR(n)variabile (max 65.535)Nel data base occupa solo lo spazio dwi caratteri effettivi + 1 o 2 byte necessari per indicare la lunghezza della stringa Nomi, cognomi, email, indirizzi – testi di lunghezza variabile.
TEXT65.535 caratteriTesto lungoArticoli, descrizioni lunghe, commenti.
LONGTEXT4 GBTesto lunghissimoLibri, documenti, log.
ENUM('val1','val2')fino a 65.535 valoriLista chiusa di opzioniesempi: Stato ordine ('annullato','in lavorazione','spedito','consegnato'), Esito scrutinio ('Promosso','Rimandato','Bocciato').
TipoFormato SQLEsempio di inserimentoDescrizione
DATE'YYYY-MM-DD''2026-03-26'Solo data (anno-mese-giorno). Usalo per date di nascita, scadenze.
TIME'HH:MM:SS''14:30:00'Solo ora (ore:minuti:secondi). Per durate o orari fissi.
DATETIME'YYYY-MM-DD HH:MM:SS''2026-03-26 14:30:00'Data e ora insieme. Non cambia con il fuso orario. Ideale per eventi.
TIMESTAMP'YYYY-MM-DD HH:MM:SS''2026-03-26 14:30:00'Memorizza un istante (secondi dal 1970). Viene convertito automaticamente nel fuso orario della connessione. Ottimo per "creato il" o "modificato il".
YEAR'YYYY''2026'Solo anno (4 cifre). Per campi come "anno di laurea".
📅 Come scrivere le date in SQL
Le date e gli orari vanno sempre scritti tra apici singoli, nel formato standard ISO 8601 aaaa/mm/gg ove a è cifra per anno, m cifra per mese, g cifra per giorno (es. '2026-03-26' per DATE per indicare il 26/03/2026, '2026-03-26 14:30:00' per DATETIME). Questo formato è universale e non crea ambiguità.
Esempio di inserimento: 
INSERT INTO eventi (nome, data_evento, ora_inizio)
VALUES ('Conferenza', '2026-03-26', '14:30:00');
🕒 DATETIME vs TIMESTAMP – quale scegliere?
• DATETIME: memorizza la data/ora esatta come la vedi. Non viene modificata in base al fuso orario. Perfetta per date di nascita, orari di eventi fissi, o quando devi preservare il valore originale.
• TIMESTAMP: memorizza un numero di secondi dal 1970 (UTC). Quando leggi il dato, MySQL lo converte automaticamente nel fuso orario della connessione. È ottimo per campi come data_creazione e ultima_modifica, perché puoi usare la funzione CURRENT_TIMESTAMP e l’opzione ON UPDATE CURRENT_TIMESTAMP per aggiornarlo automaticamente.
Esempio di tabella con TIMESTAMP auto‑gestito: 
CREATE TABLE articoli (
  id INT PRIMARY KEY,
  titolo VARCHAR(200),
  creato TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modificato TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

⚠️ Attenzione: TIMESTAMP ha un intervallo limitato (fino al 2038). Per date oltre il 2038 usa DATETIME.

⚠️ Errori comuni con le date
• Usare un formato sbagliato: MySQL accetta solo 'YYYY-MM-DD'. Un formato come '26/03/2026' verrà interpretato come '2026-03-26' solo se la modalità SQL lo permette, ma non è affidabile.
• Dimenticare gli apici: WHERE data = 2026-03-26 causerà un errore perché senza apici viene visto come una sottrazione.
• Confondere DATETIME con TIMESTAMP quando si lavora con fusi orari: se la tua applicazione serve utenti in diverse time zone, TIMESTAMP è più comodo perché si adatta automaticamente.
✅ Booleani e Altri Tipi Speciali
TipoDescrizioneEsempio
BOOLEAN / BOOLAlias di TINYINT(1).Invece di usare boolean usiamo TINYINT(1) che andrà ad indicare TRUE = 1, FALSE = 0.attivo BOOLEAN DEFAULT TRUE
BLOBBinary Large Object – per file binari (immagini, PDF,video)Meglio salvare il file su disco e memorizzare in tabella solo il percorso.
JSONMemorizza oggetti JSON validi. Supporta funzioni di interrogazione.{"nome": "Mario", "preferenze": {"tema": "scuro"}}
UUIDIdentificatore univoco universale a 128 bit.550e8400-e29b-41d4-a716-446655440000
SET Insieme di valori (puoi scegliere zero o più opzioni; quindi Usa ENUM quando il campo deve assumere esattamente uno tra un insieme fisso di valori come per esitoScrutinio, statoOrdine, Sesso; Usa SET quando il campo può contenere una combinazione di valori da un insieme prefissato come permessiFile, hobby, lingue parlate ma generalmente è meglio usare una tabella che contiene i dati considerati ).Hobby('Lettura','Musica','Sport','Collezionismo').
🎯 BOOLEAN in MySQL: come funziona davvero
MySQL non ha un tipo booleano nativo. BOOLEAN è solo un sinonimo di TINYINT(1). Quando scrivi TRUE in una query, viene convertito in 1; FALSE diventa 0.
Esempio pratico:
CREATE TABLE utenti (
  id INT PRIMARY KEY,
  attivo BOOLEAN DEFAULT FALSE
);
INSERT INTO utenti (id, attivo) VALUES (1, TRUE);  -- Inserisce 1
SELECT * FROM utenti WHERE attivo;            -- Corretto, restituisce i riga con attivo=1
SELECT * FROM utenti WHERE attivo = TRUE;     -- Stessa cosa
SELECT * FROM utenti WHERE attivo = 1;          -- Equivalente
⚠️ Attenzione: TINYINT(1) tecnicamente può contenere qualsiasi numero da 0 a 255 (se UNSIGNED). Per coerenza, usa solo 0/1 e sfrutta la sintassi TRUE/FALSE per rendere il codice più leggibile.
🔒 Vincoli (Constraints) – Regole per proteggere i dati

I vincoli definiscono regole aggiuntive per le colonne, garantendo l'integrità dei dati. Ecco i più importanti, con esempi:

NOT NULL
La colonna non può essere vuota (NULL).
Es: nome VARCHAR(50) NOT NULL
UNIQUE
Impedisce duplicati.
Es: email VARCHAR(100) UNIQUE
DEFAULT
Valore predefinito se non specificato.
Es: attivo BOOLEAN DEFAULT TRUE
PRIMARY KEY
Identifica univocamente ogni riga (combina NOT NULL e UNIQUE).
Es: CF char(16)PRIMARY KEY
FOREIGN KEY
Collega due tabelle (integrità referenziale).
Es: utente_id INT REFERENCES utenti(id) ON DELETE CASCADE
CHECK
Verifica una condizione.
Es: CHECK (eta >= 18)
-- Esempio completo di tabella con vincoli
CREATE TABLE utenti (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  data_registrazione DATETIME DEFAULT CURRENT_TIMESTAMP,
  attivo BOOLEAN DEFAULT TRUE,
  eta TINYINT UNSIGNED CHECK (eta >= 18)
);
💡 Esempi Pratici di CREATE TABLE (commentati)
Tabella Utenti 
-- Memorizza gli account utente
CREATE TABLE utenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    --Chiave primaria, numeri positivi, si autoincrementa 
  username VARCHAR(30) NOT NULL UNIQUE
    -- Nome utente, massimo 30 caratteri, obbligatorio e unico
  email VARCHAR(100) NOT NULL UNIQUE,
    -- Email, obbligatoria e senza duplicati
  password_hash VARCHAR(255) NOT NULL,
    -- Hash della password, lunghezza standard (es. bcrypt)
  data_registrazione DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- Data di iscrizione, automatica se non fornita
  attivo BOOLEAN DEFAULT TRUE,
    -- Account attivo? 1=attivo, 0=disabilitato
  eta TINYINT UNSIGNED CHECK (eta >= 18)
    -- Età intera positiva, vincolo per maggiorenni
);
Tabella Prodotti 
-- Catalogo prodotti
CREATE TABLE prodotti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
    -- Nome prodotto, massimo 100 caratteri
  descrizione TEXT,
    -- Descrizione lunga, può essere vuota
  prezzo DECIMAL(10,2) NOT NULL CHECK (prezzo >= 0),
    -- Prezzo con 2 decimali, obbligatorio e non negativo
  quantita SMALLINT UNSIGNED DEFAULT 0,
    -- Scorte in magazzino, positivo, default 0
  categoria ENUM('elettronica','abbigliamento','casa')
    -- Lista chiusa di categorie predefinite
);
Tabella Ordini 
-- Ordini effettuati dagli utenti
CREATE TABLE ordini (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -- ID grande per molti ordini nel tempo
  utente_id INT UNSIGNED NOT NULL,
    -- Riferimento all'utente che ha fatto l'ordine
  data_ordine DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- Data di creazione ordine
  totale DECIMAL(10,2) NOT NULL CHECK (totale >= 0),
    -- Importo totale dell'ordine
  stato VARCHAR(20) DEFAULT 'pending',
    -- Stato (pending, shipped, delivered, cancelled)
  FOREIGN KEY (utente_id) REFERENCES utenti(id) ON DELETE CASCADE
    -- Se elimino l'utente, vengono eliminati anche i suoi ordini
);
Tabella Dipendenti (esempio CHAR) 
-- Dipendenti aziendali
CREATE TABLE dipendenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(50) NOT NULL,
  cognome VARCHAR(50) NOT NULL,
  stipendio DECIMAL(10,2) NOT NULL CHECK (stipendio >= 0),
  data_assunzione DATE NOT NULL,
    -- Solo data, non serve ora
  codice_fiscale CHAR(16) NOT NULL UNIQUE
    -- Codice fiscale italiano: sempre 16 caratteri fissi → CHAR perfetto
);
⚠️ Errori comuni da evitare (per principianti)
  • Usare VARCHAR per campi a lunghezza fissa: ad esempio codice fiscale (16), codice postale (5) → sprechi spazio e performance. Usa CHAR.
  • Memorizzare i soldi in FLOAT/DOUBLE: porta a errori di arrotondamento (es. 0.1+0.2). Usa DECIMAL.
  • Dimenticare NOT NULL su campi obbligatori: se non metti NOT NULL, il database accetterà valori NULL, causando poi errori nelle applicazioni.
  • Non usare UNSIGNED per numeri che non possono essere negativi: ad esempio ID, età, quantità. Raddoppia il massimo valore positivo e evita controlli extra.
  • Usare TEXT su colonne che devono essere indicizzate: MySQL non può indicizzare completamente un TEXT se non con prefissi. Per ricerche veloci, se il testo è breve (< 255), usa VARCHAR.
  • Ignorare il set di caratteri: se hai bisogno di emoji o caratteri accentati, imposta il database/tabella su utf8mb4 (non solo utf8).
  • Errori con le date: usare formati sbagliati o dimenticare gli apici. Ricorda il formato 'YYYY-MM-DD'.
📌 Riepilogo: come scegliere il tipo giusto in 5 punti
  1. ID e contatori: INT UNSIGNED AUTO_INCREMENT (o BIGINT se prevedi miliardi di riga).
  2. Soldi e prezzi: DECIMAL(p,s) con p sufficiente (es. DECIMAL(10,2)).
  3. Testi brevi (nome, email): VARCHAR(n) con n ragionevole (50-255).
  4. Codici a lunghezza fissa (codice fiscale, targa): CHAR(n).
  5. Flag vero/falso: BOOLEAN (alias di TINYINT(1)) – usa TRUE/FALSE nelle query.

Ricorda sempre di aggiungere NOT NULL quando il campo è obbligatorio e UNSIGNED per numeri positivi.

📌 Questa guida è basata su MySQL/MariaDB. Altri DBMS (PostgreSQL, SQLite, SQL Server) hanno piccole differenze, ma i concetti fondamentali sono gli stessi.

Sintassi

CREATE TABLE nome_tabella ( nome_colonna1 tipo_dato [vincoli], nome_colonna2 tipo_dato [vincoli], ... );

Esempio Pratico

CREATE TABLE dipendenti (
id INT PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
stipendio DECIMAL(10,2),
data_assunzione DATE
);
Prima (tabella inesistente)
❌ La tabella "dipendenti" non esiste
➜
Dopo
id nome cognome stipendio data_assunzione
Tabella creata (0 righe)
Nota

I vincoli principali sono: PRIMARY KEY (chiave primaria univoca), NOT NULL (valore obbligatorio), UNIQUE (valore univoco), FOREIGN KEY (chiave esterna), DEFAULT (valore predefinito).

DDL

ALTER TABLE ADD

Descrizione

Aggiunge una nuova colonna a una tabella esistente.

Sintassi

ALTER TABLE nome_tabella ADD nome_colonna tipo_dato [vincoli];

Esempio Pratico

ALTER TABLE dipendenti
ADD email VARCHAR(100);
Prima
id nome cognome stipendio
1 Mario Rossi 2500.00
2 Laura Bianchi 2800.00
➜
Dopo
id nome cognome stipendio email
1 Mario Rossi 2500.00 NULL
2 Laura Bianchi 2800.00 NULL
Nota

La nuova colonna viene aggiunta con valore NULL per tutte le righe esistenti, a meno che non si specifichi un valore DEFAULT.

DDL

ALTER TABLE MODIFY

Descrizione

Modifica il tipo di dato o i vincoli di una colonna esistente.

Sintassi

-- MySQL / Oracle ALTER TABLE nome_tabella MODIFY nome_colonna nuovo_tipo_dato;

Esempio Pratico

ALTER TABLE dipendenti
MODIFY nome VARCHAR(100);
Struttura Prima
nome VARCHAR(50)
➜
Struttura Dopo
nome VARCHAR(100)
Attenzione

Modificare il tipo di una colonna può causare perdita di dati se il nuovo tipo è incompatibile o più piccolo del precedente.

DDL

ALTER TABLE DROP COLUMN

Descrizione

Rimuove una colonna esistente dalla tabella, eliminando tutti i dati contenuti in essa.

Sintassi

ALTER TABLE nome_tabella DROP COLUMN nome_colonna;

Esempio Pratico

ALTER TABLE dipendenti
DROP COLUMN email;
Prima
id nome cognome email
1 Mario Rossi mario@email.it
2 Laura Bianchi laura@email.it
➜
Dopo
id nome cognome
1 Mario Rossi
2 Laura Bianchi
Attenzione

L'operazione è irreversibile! Tutti i dati della colonna verranno persi definitivamente.

DDL

ALTER TABLE COMMENT

Descrizione

Aggiunge metadati descrittivi alla tabella o alle colonne. In MySQL/MariaDB, i commenti sono molto utili per documentare il database (es. spiegare unità di misura o codici).

Sintassi

-- Commento sull'intera tabella ALTER TABLE nome_tabella COMMENT = 'Testo del commento'; -- Commento su una specifica colonna (richiede ridefinizione tipo) ALTER TABLE nome_tabella MODIFY nome_colonna tipo_dato COMMENT 'Testo del commento';

Esempio 1: Commento alla Tabella

ALTER TABLE dipendenti
COMMENT = 'Registro storico del personale attivo';

Esempio 2: Commento alla Colonna

ALTER TABLE dipendenti
MODIFY stipendio DECIMAL(10,2) COMMENT 'Importo mensile lordo in Euro';
Metadati Prima le due istruzioni ALTER
dipendenti
stipendio DECIMAL
➜
Metadati Dopo le due istruzioni ALTER
dipendenti
💬 "Registro storico..."
stipendio DECIMAL 💬 "Importo mensile..."
Visualizzare i commenti

Per vedere i commenti inseriti puoi usare il comando: SHOW FULL COLUMNS FROM dipendenti; oppure SHOW CREATE TABLE dipendenti;

DDL

DROP TABLE

Descrizione

Elimina completamente una tabella dal database, inclusa la sua struttura e tutti i dati.

Sintassi

DROP TABLE nome_tabella;

Esempio Pratico

DROP TABLE dipendenti;
Prima
id nome cognome
1 Mario Rossi
2 Laura Bianchi
Tabella e dati che verranno eliminati da DROP TABLE
➜
Dopo
❌ La tabella "dipendenti" non esiste più
Attenzione

Operazione estremamente distruttiva e irreversibile! La tabella e tutti i suoi dati vengono eliminati permanentemente.

DDL

TRUNCATE TABLE

Descrizione

Svuota completamente una tabella eliminando tutte le righe, ma mantenendo la struttura intatta.

Sintassi

TRUNCATE TABLE nome_tabella;

Esempio Pratico

TRUNCATE TABLE dipendenti;
Prima
id nome cognome stipendio
1 Mario Rossi 2500.00
2 Laura Bianchi 2800.00
3 Giuseppe Verdi 3000.00
Righe che verranno eliminate da TRUNCATE
➜
Dopo
id nome cognome stipendio
Tabella vuota (0 righe)
Differenza con DELETE

TRUNCATE è più veloce di DELETE perché non registra ogni singola eliminazione nel log. Inoltre, resetta i contatori AUTO_INCREMENT. Non può essere usato con clausole WHERE.

📝 DML - Data Manipulation Language

DML

INSERT INTO

Descrizione

Inserisce una nuova riga (riga) in una tabella esistente.

Sintassi

-- Specificando le colonne INSERT INTO nome_tabella (colonna1, colonna2, ...) VALUES (valore1, valore2, ...); -- Senza specificare le colonne (tutte in ordine) INSERT INTO nome_tabella VALUES (valore1, valore2, ...);

Esempio Pratico

INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione)
VALUES (3, 'Anna', 'Neri', 2700.00, '2024-01-15');
Prima
id nome cognome stipendio data_assunzione
1 Mario Rossi 2500.00 2023-03-01
2 Laura Bianchi 2800.00 2023-06-15
➜
Dopo
id nome cognome stipendio data_assunzione
1 Mario Rossi 2500.00 2023-03-01
2 Laura Bianchi 2800.00 2023-06-15
3 Anna Neri 2700.00 2024-01-15
DML

INSERT INTO – Più Righe

Esempio: Inserimento di Più Righe

INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione)
VALUES
  (4, 'Luca', 'Ferrari', 2600.00, '2024-03-01'),
  (5, 'Sofia', 'Ricci', 2900.00, '2024-02-10'),
  (6, 'Marco', 'Conti', 3100.00, '2024-04-05');
Prima
id nome cognome stipendio data_assunzione
1 Mario Rossi 3000.00 2023-03-01
2 Laura Bianchi 2800.00 2023-06-15
➜
Dopo
id nome cognome stipendio data_assunzione
1 Mario Rossi 3000.00 2023-03-01
2 Laura Bianchi 2800.00 2023-06-15
4 Luca Ferrari 2600.00 2024-03-01
5 Sofia Ricci 2900.00 2024-02-10
6 Marco Conti 3100.00 2024-04-05
Nota

Con un unico comando INSERT INTO ... VALUES puoi aggiungere molteplici righe separandole con la virgola. È più efficiente di tanti comandi singoli ed è supportato da tutti i principali DBMS (MySQL, PostgreSQL, SQLite, SQL Server).

DML

UPDATE

Descrizione

Modifica i dati esistenti in una o più righe di una tabella.

Sintassi

UPDATE nome_tabella SET colonna1 = valore1, colonna2 = valore2 WHERE condizione;

Esempio Pratico

UPDATE dipendenti
SET stipendio = 3000.00
WHERE id = 1;
Prima
id nome cognome stipendio
1 Mario Rossi 2500.00
2 Laura Bianchi 2800.00
3 Anna Neri 2700.00
➜
Dopo
id nome cognome stipendio
1 Mario Rossi 3000.00
2 Laura Bianchi 2800.00
3 Anna Neri 2700.00
Attenzione

Senza la clausola WHERE, l'UPDATE modifica TUTTE le righe della tabella! Verifica sempre la condizione prima di eseguire.

DML

UPDATE – Più Campi

Esempio: Aggiornamento di Più Campi

UPDATE dipendenti
SET
  stipendio = 3200.00,
  email = 'mario.rossi@azienda.it'
WHERE id = 1;
Prima
id nome cognome stipendio email
1 Mario Rossi 3000.00 NULL
2 Laura Bianchi 2800.00 laura@email.it
➜
Dopo
id nome cognome stipendio email
1 Mario Rossi 3200.00 mario.rossi@azienda.it
2 Laura Bianchi 2800.00 laura@email.it
Sintassi Multi-colonna

Nella clausola SET puoi elencare più assegnazioni separate da virgola. Tutti i campi specificati vengono aggiornati contemporaneamente — ma devono appartenere alla stessa tabella. Un singolo comando UPDATE non può modificare colonne di tabelle diverse.

DML

DELETE

Descrizione

Elimina una o più righe da una tabella in base a una condizione specifica.

Sintassi

DELETE FROM nome_tabella WHERE condizione;

Esempio Pratico

DELETE FROM dipendenti
WHERE id = 3;
Prima
id nome cognome stipendio
1 Mario Rossi 3000.00
2 Laura Bianchi 2800.00
3 Anna Neri 2700.00
Riga che verrà eliminata (id=3)
➜
Dopo
id nome cognome stipendio
1 Mario Rossi 3000.00
2 Laura Bianchi 2800.00
Attenzione

Senza la clausola WHERE, DELETE elimina TUTTE le righe della tabella! Questa operazione è irreversibile senza un backup o transazione attiva.

DELETE vs TRUNCATE

DELETE: elimina righe una per una, può usare WHERE, più lento, può essere annullato in una transazione.
TRUNCATE: elimina tutte le righe in blocco, più veloce, resetta AUTO_INCREMENT, non può usare WHERE.

🔧 DDL Avanzato - MySQL/MariaDB

Il Data Definition Language (DDL) rappresenta il cuore della gestione della struttura del database in MySQL e MariaDB. Mentre le operazioni base permettono di creare tabelle semplici, il DDL avanzato consente di implementare soluzioni sofisticate per l'organizzazione dei dati, l'ottimizzazione delle performance e il mantenimento dell'integrità referenziale. Questa sezione esplora le istruzioni più potenti e frequentemente utilizzate dai database administrator e dagli sviluppatori esperti, fornendo esempi pratici e best practice per ogni comando.

---
DDL

CREATE INDEX

Descrizione

L'istruzione CREATE INDEX è uno degli strumenti più potenti per ottimizzare le performance delle query in MySQL e MariaDB. Un indice è una struttura dati che permette al database di trovare righe specifiche senza dover eseguire una scansione completa della tabella (full table scan). Pensate a un indice come all'indice analitico di un libro: invece di leggere ogni pagina per trovare un'informazione, potete consultare l'indice e saltare direttamente alle pagine rilevanti.

Gli indici in MySQL e MariaDB sono implementati come B-tree (Balanced Tree), una struttura dati che mantiene i dati ordinati e permette ricerche, inserimenti e cancellazioni in tempo logaritmico. Questa implementazione è ottimizzata per funzionare efficacemente con i dischi rigidi tradizionali, minimizzando il numero di accessi al disco necessari per ogni operazione di ricerca.

Tuttavia, gli indici non sono gratuiti in termini di risorse. Ogni indice occupa spazio su disco (a volte significativo, quanto i dati stessi) e ogni operazione di inserimento, aggiornamento o cancellazione deve aggiornare tutti gli indici associati alla tabella. Per questo motivo, è fondamentale trovare il giusto equilibrio tra il numero di indici e le performance delle operazioni di scrittura.

Sintassi

CREATE INDEX nome_indice ON nome_tabella (colonna1, colonna2);

Esempi Pratici

CREATE INDEX idx_cognome

ON dipendenti (cognome);

Spiegazione: Questo comando crea un indice sulla colonna cognome della tabella dipendenti e lo nomina idx_cognome. Dopo la creazione, le query che cercano o ordinano per cognome saranno significativamente più rapide. Ad esempio, una query come SELECT * FROM dipendenti WHERE cognome = 'Rossi' non dovrà più scansionare tutte le righe della tabella, ma potrà utilizzare l'indice per trovare direttamente le righe corrispondenti. Questo tipo di indice è ideale per colonne con alta cardinalità (molti valori diversi) e frequentemente utilizzate nelle clausole WHERE.

Prima (senza indice)
id nome cognome reparto
1 Mario Rossi Amministrazione
2 Laura Bianchi Vendite
3 Giuseppe Verdi Produzione
4 Anna Rossi Marketing
5 Marco Neri Amministrazione

Query: SELECT * FROM dipendenti WHERE cognome = 'Rossi'

Tempo: Scansione completa di 5 righe

Risultato: 2 righe trovate

➜
Dopo (con indice)
id nome cognome reparto
1 Mario Rossi Amministrazione
2 Laura Bianchi Vendite
3 Giuseppe Verdi Produzione
4 Anna Rossi Marketing
5 Marco Neri Amministrazione

📑 Indice idx_cognito creato:

Cognome Puntatore
Bianchi ➜ Riga 2
Neri ➜ Riga 5
Rossi ➜ Riga 1, 4
Verdi ➜ Riga 3

Query: SELECT * FROM dipendenti WHERE cognome = 'Rossi'

Tempo: Ricerca diretta nell'indice → accesso immediato

Risultato: 2 righe trovate

CREATE INDEX idx_reparto_data

ON dipendenti (reparto_id, data_assunzione);

Spiegazione: Questo comando crea un indice composto che include due colonne: reparto_id e data_assunzione. Un indice composto è particolarmente efficace quando le query filtrano su più colonne contemporaneamente. L'ordine delle colonne nell'indice è fondamentale: le colonne più frequentemente utilizzate nei filtri WHERE dovrebbero apparire per prime. In questo caso, l'indice è ottimizzato per query come SELECT * FROM dipendenti WHERE reparto_id = 5 ORDER BY data_assunzione DESC. L'indice permette sia il filtraggio per reparto che l'ordinamento per data_assunzione senza necessità di operazioni aggiuntive di sort.

Prima (senza indice)
id nome reparto_id data_assunzione
1 Mario 1 2023-03-01
2 Laura 2 2022-11-15
3 Giuseppe 1 2023-01-20
4 Anna 2 2023-06-10
5 Marco 3 2021-09-05

Query: SELECT * FROM dipendenti WHERE reparto_id = 1 ORDER BY data_assunzione

Operazioni: Filtro + Sort su 5 righe

Tempo: O(n log n) per l'ordinamento
➜
Dopo (con indice composto)
id nome reparto_id data_assunzione
1 Mario 1 2023-03-01
2 Laura 2 2022-11-15
3 Giuseppe 1 2023-01-20
4 Anna 2 2023-06-10
5 Marco 3 2021-09-05

📑 Indice idx_reparto_data creato:

reparto_id data_assunzione Puntatore
1 2023-01-20 ➜ Riga 3
1 2023-03-01 ➜ Riga 1
2 2022-11-15 ➜ Riga 2
2 2023-06-10 ➜ Riga 4
3 2021-09-05 ➜ Riga 5
Query: SELECT * FROM dipendenti WHERE reparto_id = 1 ORDER BY data_assunzione

Operazioni: Ricerca diretta + dati già ordinati

Tempo: O(log n) per la ricerca

 
CREATE UNIQUE INDEX idx_email_unica

ON utenti (email);

Spiegazione: Questo comando crea un indice univoco sulla colonna email della tabella utenti. L'indice univoco non solo ottimizza le ricerche, ma garantisce anche che ogni valore nella colonna indicizzata sia univoco in tutta la tabella. Questo comando fallirà se si tenta di inserire due utenti con la stessa email, fornendo un layer di validazione automatico a livello di database. È fondamentale per implementare vincoli di unicità su colonne come email, codice fiscale, numero di partita IVA, o qualsiasi altro identificativo che deve essere unico per ogni riga.

Prima (solo vincolo NOT NULL)
id nome email
1 Mario mario@email.com
2 Laura laura@email.com
3 Anonimo laura@email.com

Problema: Duplicato non rilevato!
➜
Dopo (con indice univoco)
id nome email
1 Mario mario@email.com
2 Laura laura@email.com
3 Tentativo inserimento laura@email.com

❌ ERRORE: Duplicate entry 'laura@email.com' for key 'idx_email_unica'

Inserimento bloccato dall'indice univoco!

CREATE FULLTEXT INDEX idx_contenuto_articolo

ON articoli (titolo, contenuto);

Spiegazione: Questo comando crea un indice FULLTEXT sulle colonne titolo e contenuto della tabella articoli. L'indice FULLTEXT permette ricerche testuali avanzate utilizzando la funzione MATCH AGAINST, che consente di trovare articoli basandosi sul significato delle parole e sulla loro rilevanza, non solo su corrispondenze esatte. Questo tipo di indice è fondamentale per implementare funzionalità di ricerca come quelle presenti nei blog, nei CMS, o nei motori di ricerca interni. Supporta ricerche per parole singole, frasi, boolean mode, e ricerche con ranking di rilevanza.

Prima (LIKE '%...%')
id titolo contenuto
1 Introduzione a MySQL MySQL è un database relazionale molto popolare...
2 Guida SQL Impara SQL con esempi pratici e tutorial...
3 Database MySQL Questo articolo spiega come ottimizzare MySQL...

Query: SELECT * FROM articoli WHERE contenuto LIKE '%database%'

Problema: Scansione completa, nessun ranking di rilevanza

➜
Dopo (FULLTEXT con MATCH AGAINST)
id titolo contenuto
1 Introduzione a MySQL MySQL è un database relazionale molto popolare...
2 Guida SQL Impara SQL con esempi pratici e tutorial...
3 Database MySQL Questo articolo spiega come ottimizzare MySQL...

Query: SELECT titolo, MATCH(titolo, contenuto) AGAINST('database MySQL') AS rilevanza FROM articoli WHERE MATCH(titolo, contenuto) AGAINST('database MySQL') ORDER BY rilevanza DESC

Risultato: Articoli ordinati per rilevanza (database MySQL = rilevanza alta)

Quando utilizzare gli indici

Gli indici sono particolarmente efficaci nelle seguenti situazioni:

  • **Colonne in WHERE**: Le colonne frequentemente utilizzate nelle clausole WHERE traggono grande beneficio dagli indici
  • **Join**: Le colonne usate per unire tabelle (JOIN ON) dovrebbero sempre essere indicizzate
  • **ORDER BY**: Gli indici possono eliminare la necessità di un file sort esplicito
  • **GROUP BY**: Simili agli ORDER BY, gli indici possono velocizzare le aggregazioni
  • **Colonne UNIQUE**: Le constraint di unicità richiedono implicitamente un indice univoco
Attenzione alle performance

Nonostante i benefici, gli indici hanno un costo:

  • **Spazio disco**: Ogni indice occupa spazio, a volte quanto i dati stessi
  • **Rallentamento scritture**: Ogni INSERT, UPDATE, DELETE deve aggiornare tutti gli indici della tabella
  • **Overhead di manutenzione**: Il database deve mantenere gli indici bilanciati
  • **Piano di esecuzione complesso**: Troppi indici possono confondere l'ottimizzatore del query planner

Una regola empirica è creare indici solo quando si hanno problemi di performance documentati, piuttosto che creare indici preventivamente per ogni possibile utilizzo futuro.

Verifica dell'utilizzo degli indici

Per verificare se le query stanno utilizzando gli indici creati, puoi utilizzare il comando EXPLAIN:

Ad esempio se vogliamo verificare se la query SELECT * FROM dipendenti WHERE cognome = 'Rossi'; utilizza un indice usiamo l'istruzione: 
EXPLAIN

SELECT * FROM dipendenti WHERE cognome = 'Rossi';

L'output della query soprastante mostrerà se l'indice viene utilizzato e come. Supponiamo che la tabella dipendenti non abbia un indice sulla colonna cognome. In tal caso l’output potrebbe essere: "Seq Scan on dipendenti (cost=0.00..15.00 rows=2 width=128) Filter: (cognome = 'Rossi'::text)"

Se invece esiste un indice su cognome , l'output potrebbe essere del tipo: "Index Scan using idx_cognome on dipendenti (cost=0.28..8.30 rows=2 width=128) Index Cond: (cognome = 'Rossi'::text)" e quest'ultimo evidentemente mostra l'uso dell'indice.
DDL

ALTER TABLE RENAME COLUMN

Descrizione

L'istruzione ALTER TABLE RENAME COLUMN è disponibile a partire da MySQL 8.0 e rappresenta un miglioramento significativo rispetto ai metodi precedenti per rinominare colonne. Questa operazione modifica il nome di una colonna esistente senza alterarne il tipo di dato, i vincoli, gli indici o i dati contenuti. La modifica è istantanea e non richiede la ricostruzione della tabella o la rielaborazione dei dati.

Prima di MySQL 8.0, rinominare una colonna richiedeva tecniche più complesse e rischiose, come creare una nuova colonna con il nome desiderato, copiare i dati, e poi eliminare la colonna originale. Questo approccio era lento, occupava spazio temporaneo significativo e poteva causare problemi con le applicazioni che accedevano alla tabella durante la migrazione.

La nuova sintassi RENAME COLUMN è atomica e sicura: la tabella rimane accessibile durante l'operazione, e se qualcosa va storto, la transazione può essere annullata. Inoltre, gli indici che fanno riferimento alla colonna vengono automaticamente aggiornati per riflettere il nuovo nome, eliminando la necessità di interventi manuali.

È importante notare che questa operazione non modifica il contenuto o la struttura dei dati, ma solo il nome della colonna. Tutti gli indici, le chiavi esterne, le viste e le stored procedure che facevano riferimento alla colonna continueranno a funzionare correttamente dopo la rinominazione.

Sintassi (MySQL 8.0+)

ALTER TABLE nome_tabella RENAME COLUMN vecchio_nome TO nuovo_nome;

Esempi Pratici

ALTER TABLE dipendenti

RENAME COLUMN data_assunzione TO assunzione;

Spiegazione: Questo comando rinomina la colonna data_assunzione in assunzione nella tabella dipendenti. La modifica è istantanea e tutti i dati precedentemente contenuti nella colonna rimangono intatti. Questo tipo di rinominazione è utile quando si desidera utilizzare nomi più brevi e concisi che rendano le query più leggibili. L'operazione non richiede downtime e la tabella rimane accessibile per letture e scritture durante l'esecuzione del comando.

Prima
id nome cognome data_assunzione
1 Mario Rossi 2023-03-01
2 Laura Bianchi 2022-11-15
3 Giuseppe Verdi 2023-01-20
➜
Dopo
id nome cognome assunzione
1 Mario Rossi 2023-03-01
2 Laura Bianchi 2022-11-15
3 Giuseppe Verdi 2023-01-20 
ALTER TABLE clienti

RENAME COLUMN ragione_sociale TO denominazione;

Spiegazione: Questo comando rinomina la colonna ragione_sociale in denominazione nella tabella clienti. In questo esempio, la colonna viene rinominata per conformarsi a uno standard di naming più conciso e moderno. Questo tipo di modifica è comune durante le fasi di refactoring o quando si consolidano database ereditati che utilizzano terminologie datate. Il nuovo nome "denominazione" è più breve e altrettanto chiaro, migliorando la leggibilità delle query SQL.

Prima
id ragione_sociale P.IVA
1 Azienda Alpha S.r.l. 12345678901
2 Beta Industries S.p.A. 98765432109
➜
Dopo
id denominazione P.IVA
1 Azienda Alpha S.r.l. 12345678901
2 Beta Industries S.p.A. 98765432109 
ALTER TABLE ordini

RENAME COLUMN dt_creazione TO data_creazione_ordine;

Spiegazione: Questo comando rinomina la colonna dt_creazione in data_creazione_ordine nella tabella ordini. Questo esempio mostra come rendere il nome della colonna più descrittivo aggiungendo contesto. Un nome più lungo ma chiaro migliora la manutenibilità del codice e riduce la possibilità di errori quando gli sviluppatori scrivono query, specialmente quando lavorano su tabelle con molte colonne che potrebbero avere nomi simili. La convenzione "data_" rende immediatamente chiaro che si tratta di una data, mentre "_ordine" specifica l'entità a cui si riferisce.

Prima
id dt_creazione totale
100 2024-01-15 150.00
101 2024-01-16 230.50
➜
Dopo
id data_creazione_ordine totale
100 2024-01-15 150.00
101 2024-01-16 230.50
Nota per versioni precedenti

Se stai utilizzando MySQL 5.7 o MariaDB, puoi ottenere lo stesso risultato con una procedura in tre passaggi:

-- Passo 1: Aggiungi la nuova colonna con lo stesso tipo

ALTER TABLE dipendenti

ADD COLUMN assunzione DATE AFTER cognome;

-- Passo 2: Copia i dati dalla vecchia colonna

UPDATE dipendenti SET assunzione = data_assunzione;

-- Passo 3: Elimina la colonna originale

ALTER TABLE dipendenti

DROP COLUMN data_assunzione;

Questa procedura richiede più tempo e spazio temporaneo, specialmente per tabelle di grandi dimensioni, ed è per questo che la sintassi RENAME COLUMN è preferibile quando disponibile.

Impatto su applicazioni e codice

Prima di rinominare una colonna in un ambiente di produzione, considera:

  • **Query SQL**: Tutte le query che fanno riferimento alla colonna devono essere aggiornate
  • **ORM**: Se usi un Object-Relational Mapper, aggiorna i mapping delle entità
  • **API**: Se la colonna è esposta tramite API, documenta il cambiamento
  • **Viste e stored procedure**: Verifica e aggiorna tutti gli oggetti dipendenti
  • **Backup**: Assicurati che i backup recenti possano essere ripristinati correttamente

Una strategia consigliata è creare prima la nuova colonna con il nome corretto, mantenere entrambe le colonne durante una fase di transizione, e poi rimuovere la vecchia colonna dopo aver verificato che tutto funzioni.

DDL

ALTER TABLE ADD CONSTRAINT FOREIGN KEY

Descrizione

L'istruzione ALTER TABLE ADD CONSTRAINT FOREIGN KEY è il pilastro dell'integrità referenziale in un database. Una chiave esterna crea un legame "parentela" tra due tabelle (padre e figlia), garantendo che non possano esistere riferimenti a dati inesistenti. Trasforma un semplice archivio di dati in un sistema coerente dove le relazioni sono garantite dal motore del database.

La tabella figlia contiene la chiave esterna che punta alla chiave primaria (o unica) della tabella padre. È fondamentale che i tipi di dati delle due colonne siano identici (es. entrambi INT UNSIGNED). Il motore di storage InnoDB è obbligatorio per far funzionare queste restrizioni in MySQL/MariaDB.

La potenza delle Foreign Key risiede nelle clausole ON DELETE e ON UPDATE. Esse determinano il "fato" dei dati figli quando il padre viene modificato o cancellato: scegliere CASCADE, SET NULL o RESTRICT ha implicazioni drastiche sulla logica di business e sulla sicurezza dei dati.

Sintassi

ALTER TABLE tabella_figlia ADD CONSTRAINT nome_fk FOREIGN KEY (colonna_figlia) REFERENCES tabella_padre (colonna_padre) ON DELETE [AZIONE] ON UPDATE [AZIONE];

ON DELETE e ON UPDATE sono opzionali (possono essere entrambi presenti; presenti uno dei due; assenti entrambi) e Dove [AZIONE] può essere: CASCADE, SET NULL, RESTRICT, NO ACTION.

Esempio 1:ON DELETE CASCADE (Eliminazione a cascata)

ALTER TABLE ordini

ADD CONSTRAINT fk_ordini_clienti

FOREIGN KEY (cliente_id)

REFERENCES clienti (id)

ON DELETE CASCADE

Logica: L'opzione CASCADE rende il database un "domino". Se elimini un cliente (padre), il database elimina automaticamente tutti i suoi ordini (figli). Non puoi avere un ordine senza cliente.

Stato Iniziale

Tabella: clienti

id nome
1 Mario Rossi
2 Laura Bianchi
3 Giuseppe Verdi

Tabella: ordini

id cliente_id importo
100 1 150.00
101 2 230.00
102 3 95.00
103 3 40.00
DELETE FROM clienti WHERE id = 3;
➜
Dopo (con ON DELETE CASCADE)

Tabella: clienti

id nome
1 Mario Rossi
2 Laura Bianchi

Tabella: ordini

id cliente_id importo
100 1 150.00
101 2 230.00

✅ Cliente 3 eliminato → Ordini 102 e 103 eliminati automaticamente!



Esempio 2: ON DELETE SET NULL 

ALTER TABLE dipendenti

ADD CONSTRAINT fk_dipendenti_reparti

FOREIGN KEY (reparto_id)

REFERENCES reparti (id)

ON DELETE SET NULL

Logica: Qui usiamo SET NULL. Se un reparto viene chiuso (padre eliminato), non vogliamo licenziare i dipendenti (figli). Vogliamo solo che smettano di appartenere a quel reparto. Il campo reparto_id diventa NULL. Nota: La colonna nella tabella figlia deve accettare NULL.

Prima della cancellazione(Reparto Marketing esiste)

Tabella: reparti

id nome
1 IT
2 Marketing

Tabella: dipendenti

id nome reparto_id
1 Mario Rossi 1
2 Giulia Verdi 2
➜
Dopo la cancellazione (Con ON DELETE SET NULL)

Tabella: reparti

id nome
1 IT

Tabella: dipendenti

id nome reparto_id
1 Mario Rossi 1
2 Giulia Verdi NULL

✅ Reparto marketing eliminato, ma Giulia è ancora in azienda (senza assegnazione).


Esempio 3: ON DELETE RESTRICT

ALTER TABLE prodotti

ADD CONSTRAINT fk_prodotti_categorie

FOREIGN KEY (categoria_id)

REFERENCES categorie (id)

ON DELETE RESTRICT

Logica: RESTRICT (o l'equivalente predefinito: se non scriviamo nulla è come se avessimo scritto ON DELETE RESTRICT) è il "Guardiano". Se provi a eliminare una categoria che contiene ancora prodotti, il database blocca l'operazione con un errore. Ti obbliga a gestire manualmente i prodotti prima di chiudere la categoria. È l'opzione più sicura per evitare cancellazioni accidentali.

Tentativo di eliminare categoria

Tabella: categorie

id nome
1 Informatica
2 Libri (Attiva)

Tabella: prodotti

id nome categoria_id
10 Monitor 1
11 D&D Manuale 2
12 Harry Potter 2

❌ Errore: Cannot delete or update a parent row: a foreign key constraint fails.

Non puoi eliminare la categoria "Libri" perché ci sono prodotti (ID 11, 12) collegati.

Esempio 4: Aggiornamento a cascata (ON UPDATE CASCADE)

Caso d'uso: Utilizzo di chiavi non numeriche (es. codici alfanumerici) che possono essere rinominati. Se cambia il codice del "padre", deve cambiare ovunque sia stato usato.

ALTER TABLE libri
ADD CONSTRAINT fk_libri_generi
FOREIGN KEY (codice_genere)
REFERENCES generi (codice)
ON UPDATE CASCADE;

Scenario: La tabella generi usa alcuni codici brevi ('TH' per Thriller) . Decidiamo di cambiare i codice brevi in una forma più estesa (sostituiamo 'TH' con 'THRI'; 'SCIFI' lo lasciamo inalterato). Senza CASCADE, dovremmo aggiornare manualmente migliaia di libri di genere Thriller. Con CASCADE, il database propaga la modifica.

Stato Attuale

Tabella: generi (Padre)

codice (PK) nome
TH Thriller
SCIFI Science Fiction

Tabella: libri (Figlia)

titolo codice_genere (FK)
Il silenzio degli innocenti TH
Shining TH
Mare aperto SCIFI
UPDATE generi SET codice = 'THRI' WHERE codice = 'TH';
➜
Dopo l'UPDATE

Tabella: generi

codice nome
THRI Thriller
SCIFI Sci-Fi

Tabella: libri

titolo codice_genere
Il silenzio degli innocenti THRI
Shining THRI
Mare aperto SCIFI

✅ La modifica del codice nella tabella padre si è propagata automaticamente a tutti i libri collegati!

Esempio 5: Aggiornamento con disassociazione (ON UPDATE SET NULL)

Caso d'uso: Se l'identificativo del padre cambia, non vogliamo che i figli seguano il nuovo identificativo, ma preferiamo "sganciarli" (NULL), magari per richiedere una riassegnazione manuale.

ALTER TABLE dipendenti
ADD CONSTRAINT_fk_dip_progetti
FOREIGN KEY (codice_progetto)
REFERENCES progetti (codice)
ON UPDATE SET NULL;

Scenario: Un progetto viene rinominato da 'PRJ_ALPHA' a 'PRJ_BETA'. Per policy aziendale, se un progetto cambia codice fondamentale, i dipendenti assegnati al vecchio codice vengono "sganciati" (NULL) affinché un manager possa decidere se riassegnarli al nuovo codice 'PRJ_BETA' manualmente o ad altro.

Prima dell'aggiornamento

Tabella: progetti (Padre)

codice stato
PRJ_ALPHA Attivo

Tabella: dipendenti (Figlia)

nome codice_progetto (FK)
Mario Rossi PRJ_ALPHA
Luigi Verdi PRJ_ALPHA
UPDATE progetti SET codice = 'PRJ_BETA' WHERE codice = 'PRJ_ALPHA';
➜
Dopo l'UPDATE

Tabella: progetti

codice stato
PRJ_BETA Attivo

Tabella: dipendenti

nome codice_progetto
Mario Rossi NULL
Luigi Verdi NULL

⚠️ Il codice progetto è cambiato, ma i dipendenti sono stati disassociati (NULL). Devono essere riassegnati manualmente.

Esempio 6: Aggiornamento bloccato (ON UPDATE RESTRICT)

Caso d'uso: Quando si vuole impedire la modifica della chiave primaria nella tabella padre se esistono righe collegate nella tabella figlia. Questo è il comportamento predefinito in molti database e garantisce l'integrità referenziale, evitando che i riferimenti diventino orfani ovvero che righe figlie facciano riferimento a righe individuate tramite valori della chiave primaria non esistono più .

ALTER TABLE dipendenti
ADD CONSTRAINT fk_dip_progetti
FOREIGN KEY (codice_progetto)
REFERENCES progetti (codice)
ON UPDATE RESTRICT;

Scenario: Un progetto viene rinominato da 'PRJ_ALPHA' a 'PRJ_BETA'. Tuttavia, per policy aziendale, non si permette di cambiare il codice di un progetto se ci sono dipendenti assegnati. L'update viene quindi bloccato per evitare che i dipendenti perdano il riferimento al progetto.

Prima dell'aggiornamento

Tabella: progetti (Padre)

codice stato
PRJ_ALPHA Attivo

Tabella: dipendenti (Figlia)

nome codice_progetto (FK)
Mario Rossi PRJ_ALPHA
Luigi Verdi PRJ_ALPHA
UPDATE progetti SET codice = 'PRJ_BETA' WHERE codice = 'PRJ_ALPHA';
➜
Risultato

❌ Errore di vincolo: Impossibile aggiornare la riga nella tabella 'progetti' perché esiste almeno un riga nella tabella 'dipendenti' che vi fa riferimento.

L'aggiornamento è bloccato dal vincolo RESTRICT. Per procedere, è necessario prima eliminare o riassegnare i dipendenti collegati al progetto 'PRJ_ALPHA'.

Esempio 7: Aggiornamento e cancellazione a cascata (ON UPDATE CASCADE + ON DELETE CASCADE)

Caso d'uso: Gestione di codici categoria dinamici. Quando un codice categoria viene modificato, l'aggiornamento si propaga automaticamente. Quando una categoria viene eliminata, tutti gli elementi associati vengono rimossi in sicurezza.

ALTER TABLE prodotti
ADD CONSTRAINT fk_prodotti_categorie
FOREIGN KEY (codice_categoria)
REFERENCES categorie (codice)
ON UPDATE CASCADE
ON DELETE CASCADE;

Scenario:

  • ON UPDATE CASCADE: Se il codice categoria 'ELE' viene rinominato in 'ELET', tutti i prodotti aggiornano automaticamente il riferimento.
  • ON DELETE CASCADE: Se la categoria 'ABG' (Abbigliamento) viene eliminata, tutti i prodotti associati vengono cancellati automaticamente per mantenere l'integrità referenziale.
Nota: Entrambe le clausole sono opzionali e vanno definite esplicitamente quando necessarie.

Stato Iniziale (Prima dell'UPDATE)

Tabella: categorie (Padre)

codice (PK)nome
ELEElettronica
ABGAbbigliamento

Tabella: prodotti (Figlia)

prodottocodice_categoria (FK)
Smartphone XELE
Cuffie ProELE
Maglione InvernoABG
UPDATE categorie SET codice = 'ELET' WHERE codice = 'ELE';
➜
Dopo l'UPDATE

Tabella: categorie

codicenome
ELETElettronica
ABGAbbigliamento

Tabella: prodotti

prodottocodice_categoria
Smartphone XELET
Cuffie ProELET
Maglione InvernoABG

✅ L'aggiornamento del codice categoria si è propagato automaticamente a tutti i prodotti!

----------
Stato Iniziale (Prima della DELETE)

Tabella: categorie (Padre)

codice (PK)nome
ELETElettronica
ABGAbbigliamento

Tabella: prodotti (Figlia)

prodottocodice_categoria (FK)
Smartphone XELET
Cuffie ProELET
Maglione InvernoABG
Sciarpa di LanaABG
DELETE FROM categorie WHERE codice = 'ABG';
➜
Dopo la DELETE

Tabella: categorie

codicenome
ELETElettronica

Tabella: prodotti

prodottocodice_categoria
Smartphone XELET
Cuffie ProELET

✅ L'eliminazione della categoria ha rimosso automaticamente tutti i prodotti associati!

Esempio 8: Chiave Esterna Composta (Composite Key)

ALTER TABLE movimenti_magazzino
    
    ADD CONSTRAINT fk_movimenti_inventario
    
    FOREIGN KEY (prodotto_id, magazzino_id)
    
    REFERENCES inventario (prodotto_id, magazzino_id)
    
    ON DELETE RESTRICT;

Logica: In questo caso la chiave primaria della tabella padre (inventario) è formata da due colonne. La foreign key deve riferirsi a entrambe contemporaneamente. Questo garantisce che un movimento sia valido solo per quella specifica combinazione di Prodotto e Magazzino.

Tabella Padre: inventario
prodotto_id magazzino_id quantità
101 A 50
101 B 20

Chiave primaria composta: (prodotto_id, magazzino_id)

➜
Tabella Figlia: movimenti_magazzino
id_mov prodotto_id magazzino_id tipo
900 101 A Entrata
901 101 B Uscita

Foreign key composta: (prodotto_id, magazzino_id) → inventario(prodotto_id, magazzino_id)

Cosa succede se si tenta di inserire un movimento non valido?

INSERT INTO movimenti_magazzino (id_mov, prodotto_id, magazzino_id, tipo) 
VALUES (902, 101, 'C', 'Uscita');

❌ Errore di vincolo: La coppia (101, 'C') non esiste nella tabella inventario!

Il database rifiuta l'inserimento perché la foreign key composta non trova una corrispondenza nella tabella padre.

Esempio 9: Relazione Uno-a-Uno (One-to-One)

-- Tabella 1: Persone (dati anagrafici)
CREATE TABLE persone (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    cognome VARCHAR(50) NOT NULL
);

-- Tabella 2: Passaporti (documenti)
CREATE TABLE passaporti (
    numero VARCHAR(20) PRIMARY KEY,
    persona_id INT UNIQUE,  -- Vincolo UNIQUE: 1 passaporto per persona
    data_scadenza DATE,
    FOREIGN KEY (persona_id) 
    REFERENCES persone(id) 
    ON DELETE CASCADE
);

Logica: Ogni persona può avere al massimo un passaporto, e ogni passaporto appartiene a una sola persona. Per garantire questo, la colonna persona_id nella tabella passaporti deve avere un vincolo UNIQUE. Se non ci fosse, potremmo inserire due passaporti per la stessa persona. L'opzione ON DELETE CASCADE fa sì che quando una persona viene eliminata, anche il suo passaporto venga rimosso automaticamente.

Tabella Padre: persone
id nome cognome
1 Mario Rossi
2 Luigi Verdi

Stato iniziale: due persone registrate.

⬇️ (Relazione UNICA)
Tabella Figlia: passaporti
numero persona_id (FK, UNIQUE) data_scadenza
AA1234567 1 2028-12-31
BB7654321 2 2027-06-15

Ogni passaporto è collegato a una sola persona.

✅ Mario Rossi (id=1) ha il passaporto AA1234567. Luigi Verdi (id=2) ha il passaporto BB7654321. Nessuna persona ha più di un passaporto grazie al vincolo UNIQUE.

Cosa succede se si viola il vincolo UNIQUE?

INSERT INTO passaporti (numero, persona_id, data_scadenza) 
VALUES ('CC1111111', 1, '2030-01-01');

❌ Errore: Duplicato per la chiave 'persona_id'. La persona con id=1 ha già un passaporto!

Il database impedisce di assegnare un secondo passaporto a Mario Rossi.

Comportamento con ON DELETE CASCADE

DELETE FROM persone WHERE id = 1;

✅ Mario Rossi viene eliminato dalla tabella persone. Grazie a ON DELETE CASCADE, anche il suo passaporto (AA1234567) viene rimosso automaticamente dalla tabella passaporti.

Tabella persone (dopo DELETE)
id nome cognome
2 Luigi Verdi

Rimane solo Luigi Verdi (id=2).

⬇️
Tabella passaporti (dopo DELETE con ON DELETE CASCADE)
numero persona_id data_scadenza
BB7654321 2 2027-06-15

Il passaporto AA1234567 (di Mario) è stato rimosso automaticamente.

Esempio 10: Foreign Key Auto-referenziale (Gerarchie)

ALTER TABLE dipendenti
    
    ADD CONSTRAINT fk_dipendenti_manager
    
    FOREIGN KEY (manager_id)
    
    REFERENCES dipendenti (id)
    
    ON DELETE SET NULL;

Logica: Una tabella può referenziare se stessa. È utile per strutture in caso di gerarchia (genitore -> figlio, capoUfficio -> segretario), . Un dipendente è una riga nella tabella e la sua chiave esterna ,il suo manager_id punta all'id di un'altra riga nella stessa tabella.

Tabella prima cancellazione riga Sviluppatore Senior
id (PK) nome dipendente manager_id (FK -> id) Note
10 CEO Azienda NULL Nessun manager (è il capo)
20 Resp. IT 10 Il suo manager è il CEO (id 10)
21 Sviluppatore Senior 20 Il suo manager è il Resp. IT (id 20)
22 Stagista 21 Il suo manager è lo Sviluppatore (id 21)


Tabella dopo cancellazione riga Sviluppatore Senior con ON DLETE SET NULL
id (PK) nome dipendente manager_id (FK -> id) Note
10 CEO Azienda NULL Nessun manager (è il capo)
20 Resp. IT 10 Il suo manager è il CEO (id 10)
22 Stagista NULL Il suo manager è lo Sviluppatore (id 21)

✅ Se cancelliamo lo Sviluppatore Senior (id=21), lo stagista (id=22) non viene cancellato, ma il suo manager_id diventa NULL (perde il riferimento al capo).

Opzioni ON DELETE e ON UPDATE a confronto
Azione Comportamento Quando usarlo
CASCADE PROPAGA ELIMINAZIONE/MODIFICA AI FIGLI: Con l'eliminazione(l'aggiornamento) di una riga del padre si ha l'eliminazione (l'aggiornamento) delle righe dei figli collegate Relazioni forte dipendenza (Ordine effettuato /Righe Ordine effettuato).
SET NULL IMPOSTA NULL NEL FIGLIO:Con l'eliminazione(l'aggiornamento) di una riga del padre si ha l'impostazione a null nelle righe dei figli collegate Dati storici o opzionali (Dipendenti/Reparto).
RESTRICT BLOCCA L'OPERAZIONE SUL PADRE: Se cancello/aggiorno PK una riga del padre che è collegata a righe dell'entità figlia, la cancellazione/aggiornamento PK è bloccata Sicurezza massima (Categorie/Prodotti).
NO ACTION SIMILE A RESTRICT (verifica in differita). Compatibilità SQL standard, logiche complesse.
Convenzioni per il nome dei constraint

Un buon nome per la foreign key facilita il debugging e la manutenzione:

  • Sintassi consigliata: fk_[tabella_figlia]_[tabella_padre]_[colonna_opzionale]
  • Esempio chiaro: fk_ordini_clienti dice subito che è il legame tra ordini e clienti.
  • Evitare: Nomi generici come fk1, fk_clienti (ambiguo). Usa sempre il prefisso fk_.
Gestione degli errori comuni

Creare una Foreign Key a volte fallisce. Ecco come risolvere i problemi più frequenti:

  • Errore 1215 (Cannot add foreign key): Solitamente causa da tipi di dati diversi (es. padre INT, figlio BIGINT) o mancato supporto InnoDB. Verifica che charset e collation siano identici.
  • Errore 1452 (Cannot add or update a child row): Ci sono già dati "orfani" nella tabella figlia. Prima di creare il vincolo, pulisci i dati:
-- Trova i dati sporchi che violerebbero la nuova FK

SELECT figlio.*

FROM tabella_figlio figlio

LEFT JOIN tabella_padre padre ON figlio.colonna_fk = padre.id

WHERE padre.id IS NULL AND figlio.colonna_fk IS NOT NULL;
Verifica e rimozione dei constraint

Per identificare il nome esatto del constraint (che spesso viene generato automaticamente se non specificato):

SHOW CREATE TABLE nome_tabella;

Per rimuovere il vincolo:

ALTER TABLE nome_tabella

DROP FOREIGN KEY nome_del_constraint;
Definizione dei vincoli al momento della creazione delle tabelle

I vincoli (di integrità referenziale, di dominio o di tupla) possono – e spesso è buona pratica – essere definiti direttamente durante la creazione della tabella con CREATE TABLE. Questo rende lo schema più chiaro fin dall’inizio e garantisce coerenza sin dal primo inserimento di dati.

Esempi:

-- Vincolo di dominio: valore positivo
CREATE TABLE prodotti (
id INT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
prezzo DECIMAL(10,2) CHECK (prezzo > 0)
);

Spiegazione: Il vincolo CHECK (prezzo > 0) è un esempio di vincolo di dominio: impone che il valore del campo prezzo sia sempre maggiore di zero. MySQL lo accetta nella sintassi (anche se in alcune versioni non lo applica rigorosamente a meno che non si usi il motore InnoDB con SQL mode abilitato), ma altri DBMS come PostgreSQL o SQL Server lo applicano in modo rigoroso. È comunque una buona pratica dichiararlo per chiarezza semantica.

-- Vincolo di tupla: almeno uno tra email o telefono deve essere presente
CREATE TABLE contatti (
id INT PRIMARY KEY,
email VARCHAR(100),
telefono VARCHAR(20),
CHECK (email IS NOT NULL OR telefono IS NOT NULL)
);

Spiegazione: Questo è un vincolo di tupla perché coinvolge più attributi della stessa riga. La condizione assicura che ogni riga abbia almeno un mezzo di contatto: o l’email, o il telefono (o entrambi). Senza questo vincolo, si potrebbero inserire contatti completamente inutilizzabili.



-- Vincolo di integrità referenziale (foreign key)
CREATE TABLE ordini (
id INT PRIMARY KEY,
cliente_id INT NOT NULL,
data_ordine DATE NOT NULL,
FOREIGN KEY fk_ordini_clienti (cliente_id)
REFERENCES clienti(id)
ON DELETE CASCADE
);

Spiegazione: Qui viene definito un vincolo di integrità referenziale tramite una foreign key. Il campo cliente_id deve corrispondere a un valore esistente nella colonna id della tabella clienti. L’opzione ON DELETE CASCADE specifica che, se un cliente viene eliminato, tutti i suoi ordini verranno cancellati automaticamente. Il nome del vincolo (fk_ordini_clienti) segue la convenzione suggerita, rendendo facile identificarne il ruolo durante la manutenzione.

Definire i vincoli in fase di creazione evita errori successivi e documenta esplicitamente le regole del modello dati.

Vincoli di Dominio e di Tupla con CHECK

Descrizione

I vincoli di dominio e di tupla vengono implementati in SQL utilizzando l'istruzione CHECK. Questi vincoli permettono di definire regole di validazione personalizzate sui dati inseriti o modificati in una tabella, garantendo che rispettino specifiche condizioni logiche definite dallo sviluppatore.

Vincoli di Dominio: Applicano regole di validazione su una singola colonna. Ad esempio, possono garantire che un'età sia sempre positiva, che un prezzo sia maggiore di zero, o che una data di nascita sia nel passato.

Vincoli di Tupla: Applicano regole di validazione che coinvolgono più colonne della stessa riga. Ad esempio, possono garantire che la data di fine sia successiva alla data di inizio, che lo sconto non superi il prezzo originale, o che un prodotto sia disponibile solo se la quantità è maggiore di zero.

I vincoli CHECK vengono valutati ogni volta che si tenta di inserire o modificare una riga nella tabella. Se la condizione restituisce FALSE, l'operazione viene rifiutata con un errore. Se restituisce NULL (ad esempio quando un campo coinvolto è NULL), il vincolo viene considerato soddisfatto (comportamento standard SQL).

In MySQL e MariaDB, i vincoli CHECK sono supportati a partire dalla versione 8.0.16 (MySQL) e 10.2.1 (MariaDB). Nelle versioni precedenti, la sintassi viene accettata ma il vincolo non viene applicato.

Sintassi

-- Vincolo di dominio (singola colonna) ALTER TABLE nome_tabella ADD CONSTRAINT nome_constraint CHECK (condizione_sulla_colonna); -- Vincolo di tupla (multiple colonne) ALTER TABLE nome_tabella ADD CONSTRAINT nome_constraint CHECK (condizione_su_più_colonne);

Esempi Pratici

ALTER TABLE prodotti
ADD CONSTRAINT chk_prezzo_positivo
CHECK (prezzo > 0);

Spiegazione: Questo vincolo chiamato "chk_prezzo_positivo" è un vincolo di dominio garantisce che il prezzo di ogni prodotto sia sempre maggiore di zero. Impedisce l'inserimento di prodotti con prezzo negativo o nullo, il che potrebbe causare problemi nelle applicazioni di e-commerce o nella contabilità. Nota che prezzo > 0 esclude anche lo zero, il che può essere importante per alcuni scenari di business. Se invece volessi permettere anche lo zero, dovresti usare prezzo >= 0.

Inserimento tentativo

Tabella: prodotti (prima dell'inserimento)

id nome prezzo
1 Smartphone 599.99
2 Cuffie 89.50

Tentativo: INSERT INTO prodotti (id, nome, prezzo) VALUES (3, 'Prodotto Errato', -19.99)

➜
Risultato

Tabella: prodotti (nessuna modifica)

id nome prezzo
1 Smartphone 599.99
2 Cuffie 89.50

❌ ERRORE: Check constraint 'chk_prezzo_positivo' is violated

Il prezzo negativo non è permesso dal vincolo di dominio!

ALTER TABLE dipendenti
ADD CONSTRAINT chk_email_valida
CHECK (email LIKE '%@%.%');

Spiegazione: Questo vincolo chiamato "chk_email_valida" è un vincolo di dominio verifica che l'indirizzo email contenga il carattere '@' seguito da almeno un punto. È una validazione di base del formato email. Nota che questa è una validazione sintattica molto semplice e non garantisce che l'email sia effettivamente valida o esistente. Per validazioni più complesse, potresti utilizzare espressioni regolari (se supportate dal tuo DBMS) oppure gestire la validazione a livello di applicazione.

ALTER TABLE prenotazioni
ADD CONSTRAINT chk_date_valide
CHECK (data_check_in < data_check_out);

Spiegazione: Questo vincolo chiamato "chk_date_valide" è un vincolo di tupla che coinvolge due colonne. Garantisce che la data di check-in sia sempre precedente alla data di check-out in una prenotazione alberghiera. Questo tipo di vincolo è fondamentale per prevenire errori logici nei dati. Senza questo vincolo, potrebbero essere inserite prenotazioni con date incoerenti che renderebbero il sistema inutilizzabile per la gestione delle camere.

Inserimento incoerente

Tabella: prenotazioni (prima dell'inserimento)

id camera_id data_check_in data_check_out
1 101 2024-01-15 2024-01-20

Tentativo: INSERT INTO prenotazioni (id, camera_id, data_check_in, data_check_out) VALUES (2, 102, '2024-02-10', '2024-02-05')

➜
Risultato

Tabella: prenotazioni (nessuna modifica)

id camera_id data_check_in data_check_out
1 101 2024-01-15 2024-01-20

❌ ERRORE: Check constraint 'chk_date_valide' is violated

La data di check-out non può essere precedente al check-in!

ALTER TABLE ordini
ADD CONSTRAINT chk_sconto_valido
CHECK (sconto >= 0 AND sconto <= prezzo_totale);

Spiegazione: Questo vincolo di tupla garantisce che lo sconto applicato a un ordine sia sempre compreso tra 0 e il prezzo totale. Impedisce sia sconti negativi (che aumenterebbero il prezzo) sia sconti superiori al prezzo totale (che renderebbero l'importo finale negativo). Questo è un esempio di vincolo complesso che coinvolge condizioni multiple collegate dall'operatore logico AND.

ALTER TABLE studenti
ADD CONSTRAINT chk_eta_iscrizione
CHECK (
    (tipo_iscrizione = 'universitaria' AND eta >= 18)
    OR
    (tipo_iscrizione = 'scolastica' AND eta BETWEEN 6 AND 19)
    OR
    (tipo_iscrizione = 'adulti' AND eta >= 30)
);

Spiegazione: Questo complesso vincolo di tupla implementa regole di business specifiche per diversi tipi di iscrizione. Ogni tipo di iscrizione ha una fascia di età valida differente. Gli studenti universitari devono avere almeno 18 anni, quelli scolastici devono essere tra 6 e 19 anni, mentre i corsi per adulti sono riservati a over 30. Questo esempio mostra come i vincoli CHECK possano catturare regole di dominio complesse direttamente a livello di database.

Operatori e Funzioni Disponibili nei CHECK
Categoria Esempi Utilizzo Tipico
Operatori di Confronto =, <>, <, >, <=, >= Confronto di valori numerici, date, stringhe
Operatori Logici AND, OR, NOT Combinare condizioni multiple
Operatori su Stringhe LIKE, IN, BETWEEN Validazione formati, elenchi valori accettati
Funzioni di Data CURDATE(), YEAR(), DATE_ADD() Validazione date relative al giorno corrente
Funzioni Matematiche ABS(), ROUND(), MOD() Calcoli per validazione complessa
Convenzioni per il Naming dei Vincoli CHECK

Utilizza convenzioni per dare nomi consistenti ai vincoli CHECK:

  • Prefisso standard: chk_ seguito dal nome della tabella e breve descrizione
  • Chiarezza: Il nome deve indicare immediatamente quale regola viene applicata
  • Esempi:
    • chk_clienti_email_valida - Validazione email nella tabella clienti
    • chk_prenotazioni_date_valide - Validazione coerenza date in prenotazioni
    • chk_prodotti_prezzo_positivo - Vincolo di dominio su prezzo prodotti
Gestione degli Errori Comuni

Quando aggiungi vincoli CHECK, potresti incontrare questi errori:

  • Error 3819: Check constraint is violated: Tentativo di inserire dati che non rispettano il vincolo
  • Error 3813: Column check constraint references other column: Vincolo di dominio che fa riferimento a colonne non consentite
  • Error 4025: Constraint is not valid: Vincolo malformato o con sintassi errata

Per verificare quali righe esistenti violano un nuovo vincolo CHECK prima di aggiungerlo:

-- Verifica dati esistenti contro la condizione del vincolo
SELECT * 
FROM nome_tabella
WHERE NOT (condizione_del_vincolo);
Verifica e Rimozione dei Vincoli CHECK

Per verificare i vincoli CHECK esistenti in una tabella:

SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'nome_database';

Per disabilitare temporaneamente un vincolo CHECK (se supportato):

ALTER TABLE nome_tabella
ALTER CONSTRAINT nome_constraint DISABLE;

Per rimuovere un vincolo CHECK:

ALTER TABLE nome_tabella
DROP CONSTRAINT nome_constraint;
Limitazioni e Best Practices
  • Performance: Vincoli CHECK complessi possono impattare le performance di INSERT/UPDATE
  • Portabilità: Sintassi e funzioni disponibili variano tra diversi DBMS
  • NULL Handling: Ricorda che NULL rende la condizione NULL, non FALSE
  • Livello di Validazione: Usa i CHECK per validazioni di dominio semplice; per logiche complesse, considera trigger o validazione a livello applicazione
  • Documentazione: Documenta sempre i vincoli CHECK complessi nel codice e nella documentazione del database

📝 DML Avanzato - Data Manipulation Language

Questa sezione approfondisce le funzionalità avanzate del linguaggio di manipolazione dei dati (DML), mostrando tecniche sofisticate per l'inserimento, la modifica e la cancellazione dei dati nelle tabelle del database. Ogni comando viene illustrato con esempi pratici dettagliati, analizzando riga per riga il codice SQL e presentando lo stato della tabella prima e dopo l'esecuzione dell'operazione. Le tecniche presentate sono essenziali per gestire scenari complessi di manipolazione dei dati in ambienti di produzione reali.

DML AVANZATO

INSERT con Subquery

Descrizione

Inserisce righe in una tabella utilizzando i risultati di una query SELECT. Questo metodo è ideale per copiare dati tra tabelle, popolare tabelle di backup o creare viste materializzate.

Sintassi

INSERT INTO tabella_destinazione (colonna1, colonna2, ...) SELECT colonna1, colonna2, ... FROM tabella_origine WHERE condizione;

Esempio Pratico

INSERT INTO ordini_2024 (

ordine_id, cliente_id, totale, data_ordine, stato

)

SELECT

ordine_id, 

cliente_id, 

totale, 

data_ordine, 

'COMPLETATO'

FROM ordini

WHERE YEAR(data_ordine) = 2024;
Prima (tabella ordini_2024)
ordine_id cliente_id totale data_ordine stato
Tabella vuota
➜
Dopo (tabella ordini_2024)
ordine_id cliente_id totale data_ordine stato
1001 101 250.00 2024-01-15 COMPLETATO
1002 103 180.50 2024-01-20 COMPLETATO
1005 108 320.00 2024-02-10 COMPLETATO
3 righe inserite

Analisi del Codice

INSERT INTO ordini_2024 (ordine_id, cliente_id, totale, data_ordine, stato): Questa riga specifica la tabella di destinazione (ordini_2024) e l'elenco delle colonne che verranno popolate con i nuovi dati. La specificazione esplicita delle colonne garantisce la correttezza dell'operazione anche se la struttura della tabella dovesse cambiare in futuro.

SELECT ordine_id, cliente_id, totale, data_ordine: La clausola SELECT estrae i campi specificati dalla tabella sorgente (ordini), mantenendo i valori originali per questi quattro campi.

'COMPLETATO': Per il campo stato, viene utilizzato un valore costante stringa che sovrascrive qualsiasi valore esistente nella tabella sorgente, dimostrando come sia possibile combinare dati provenienti direttamente dalle tabelle con valori calcolati o costanti durante l'inserimento.

FROM ordini WHERE YEAR(data_ordine) = 2024: La clausola WHERE filtra solo gli ordini dell'anno 2024, utilizzando la funzione YEAR per isolare la componente relativa all'anno dalla data completa. Questo approccio dimostra la potenza delle subquery, che permettono di applicare filtri complessi direttamente nel database.

Nota

Le colonne nel SELECT devono corrispondere esattamente per tipo e posizione a quelle specificate nella INSERT. È possibile omettere alcune colonne se accettano valori NULL o hanno un DEFAULT definito.

DML AVANZATO

INSERT Multi-Riga

Descrizione

Inserisce multiple righe in un'unica istruzione INSERT, migliorando le prestazioni rispetto a INSERT multiple separate. Supportato dalla maggior parte dei database moderni con sintassi leggermente diverse.

Sintassi (SQL Server / PostgreSQL)

INSERT INTO tabella (col1, col2, col3) VALUES (valore1a, valore2a, valore3a), (valore1b, valore2b, valore3b), (valore1c, valore2c, valore3c);

Esempio Pratico

INSERT INTO prodotti (

codice_prodotto, nome, categoria, prezzo, giacenza

)

VALUES

('PROD001', 'Laptop Dell XPS', 'Elettronica', 1299.99, 15),

('PROD002', 'Mouse Wireless', 'Accessori', 29.99, 100),

('PROD003', 'Monitor 27 pollici', 'Elettronica', 349.99, 8);
Prima (tabella prodotti)
codice_prodotto nome categoria prezzo giacenza
PROD000 Prodotto Esistente Varie 99.99 50
➜
Dopo (tabella prodotti)
codice_prodotto nome categoria prezzo giacenza
PROD000 Prodotto Esistente Varie 99.99 50
PROD001 Laptop Dell XPS Elettronica 1299.99 15
PROD002 Mouse Wireless Accessori 29.99 100
PROD003 Monitor 27 pollici Elettronica 349.99 8
3 righe inserite (totale: 4)

Analisi del Codice

INSERT INTO prodotti (codice_prodotto, nome, categoria, prezzo, giacenza): La riga specifica la tabella destinataria e l'elenco delle colonne in cui verranno inseriti i valori. L'ordine delle colonne determina la posizione di ogni valore nelle tuple successive.

VALUES ('PROD001', 'Laptop Dell XPS', 'Elettronica', 1299.99, 15): Ogni tupla rappresenta una riga completa da inserire. I valori stringa sono delimitati da apici singoli, mentre i valori numerici non richiedono delimitatori. Le tuple sono separate da virgole.

Efficienza dell'inserimento multi-riga: L'inserimento multi-riga riduce drasticamente l'overhead di parsing e ottimizzazione rispetto a INSERT multiple separate. Il database processa tutte le tuple come un singolo blocco logico, migliorando le prestazioni specialmente con grandi volumi di dati.

Nota

MySQL e PostgreSQL supportano la sintassi multi-valore con VALUES. Oracle richiede l'uso di SELECT UNION ALL. L'inserimento multi-riga riduce il overhead di parsing e migliora le prestazioni del database.

DML AVANZATO

UPDATE con JOIN

Descrizione

Aggiorna i dati di una tabella utilizzando valori provenienti da altre tabelle tramite operazioni di JOIN. In MariaDB/MySQL, questa funzionalità si realizza con una sintassi specifica che **non utilizza la clausola FROM** dopo UPDATE. Permette di sincronizzare dati tra tabelle correlate in modo efficiente.

Sintassi corretta per MariaDB/MySQL

UPDATE tabella1 [AS] alias1 JOIN tabella2 [AS] alias2 ON condizione_join SET tabella1.colonna = espressione [WHERE condizione];


Nota: La clausola FROM dopo UPDATE non è supportata in MariaDB/MySQL. Quella sintassi è tipica di SQL Server o PostgreSQL.

Esempio Pratico: Applicare sconto ai clienti PREMIUM

UPDATE ordini o
JOIN clienti c ON o.cliente_id = c.id
SET o.totale = o.totale * 0.9  -- applica sconto del 10%
WHERE c.tipo = 'PREMIUM'
AND o.stato = 'IN_ATTESA';
Stato iniziale delle tabelle

Tabella: clienti

id nome tipo
50 Mario Rossi PREMIUM
51 Luigi Verdi STANDARD
52 Anna Bianchi PREMIUM

Tabella: ordini

ordine_id cliente_id totale stato
2001 50 100.00 IN_ATTESA
2002 51 250.00 IN_ATTESA
2003 52 180.00 IN_ATTESA

Nota: la tabella ordini contiene solo il cliente_id (chiave esterna), non il tipo del cliente. Il tipo si recupera tramite JOIN con la tabella clienti.

⬇️ Esegui UPDATE con JOIN
Tabella: ordini (dopo l'UPDATE)
ordine_id cliente_id totale stato
2001 50 90.00 IN_ATTESA
2002 51 250.00 IN_ATTESA
2003 52 162.00 IN_ATTESA
✅ 2 righe aggiornate (sconto del 10% applicato ai clienti PREMIUM)

Analisi del Codice

UPDATE ordini o JOIN clienti c ON o.cliente_id = c.id: La clausola JOIN collega la tabella ordini (alias o) con la tabella clienti (alias c) in base alla corrispondenza tra o.cliente_id e c.id. Questo join crea un set di righe temporaneo che include solo gli ordini per i quali esiste un cliente corrispondente.

SET o.totale = o.totale * 0.9: Per ogni riga del join, il totale dell'ordine viene moltiplicato per 0.9 (applicando uno sconto del 10%).

WHERE c.tipo = 'PREMIUM' AND o.stato = 'IN_ATTESA': Il filtro seleziona solo gli ordini il cui cliente è di tipo PREMIUM e lo stato dell'ordine è IN_ATTESA. Solo queste righe vengono aggiornate.

Risultato: Gli ordini 2001 (cliente 50, PREMIUM) e 2003 (cliente 52, PREMIUM) vengono aggiornati a 90.00 e 162.00. L'ordine 2002 (cliente 51, STANDARD) rimane invariato a 250.00.

Attenzione alle differenze tra DBMS

La sintassi mostrata è **specifica per MariaDB e MySQL**. Altri database utilizzano strutture diverse:

  • SQL Server / PostgreSQL: supportano UPDATE ... FROM ... JOIN ... (con FROM dopo UPDATE).
  • SQLite: non supporta UPDATE con JOIN diretto; richiede una subquery.

Se devi scrivere codice portabile, usa sempre la sintassi del tuo database target o astrai la logica in una view/stored procedure.

DML AVANZATO

UPDATE con Subquery

Descrizione

Utilizza subquery all'interno dell'aggiornamento per calcolare i nuovi valori basandosi su condizioni complesse o aggregazioni di dati provenienti da altre tabelle.

Sintassi

UPDATE tabella SET colonna = (SELECT aggregazione FROM tabella2 WHERE condizione) WHERE EXISTS (SELECT 1 FROM tabella2 WHERE condizione);

Esempio Pratico

UPDATE prodotti

SET prezzo_vendita = (

SELECT AVG(prezzo)  

FROM prodotti_concorrenti

WHERE prodotti_concorrenti.categoria = prodotti.categoria

)

WHERE EXISTS (

SELECT 1

FROM prodotti_concorrenti

WHERE prodotti_concorrenti.categoria = prodotti.categoria

);
Prima
codice nome categoria prezzo_base prezzo_vendita
A001 Prodotto A Elettronica 100.00 120.00
A002 Prodotto B Abbigliamento 50.00 60.00
A003 Prodotto C Elettronica 200.00 240.00
Dati concorrenti (Elettronica): €85, €95, €120 (Media: €100)
Dati concorrenti (Abbigliamento): €45, €55 (Media: €50)
➜
Dopo
codice nome categoria prezzo_base prezzo_vendita
A001 Prodotto A Elettronica 100.00 120.00
A002 Prodotto B Abbigliamento 50.00 60.00
A003 Prodotto C Elettronica 200.00 120.00
3 righe aggiornate

Analisi del Codice

La query setta il prezzo di un prodotto che ha almeno un concorrente uguale alla media dei prezzi dei prodotti concorrenti SET prezzo_vendita = (SELECT AVG(prezzo) FROM prodotti_concorrenti WHERE prodotti_concorrenti.categoria = prodotti.categoria): La subquery correlata calcola il prezzo medio dei concorrenti nella stessa categoria. La condizione nella WHERE della subquery crea la correlazione con la riga esterna.

WHERE EXISTS (SELECT 1 FROM prodotti_concorrenti WHERE prodotti_concorrenti.categoria = prodotti.categoria): La clausola WHERE EXISTS garantisce che l'aggiornamento venga applicato solo ai prodotti per cui esistono dati concorrenti nella stessa categoria, evitando valori NULL (se non ci fossero prodotti concorrenti senza questa istruzione otterrei la media dei prezzi di prodotti concorrenti che non esistono, ovvero il valore NULL.

Risultato: I prodotti A001 e A003 (Elettronica) ottengono prezzo_vendita = €100, media dei prezzi dei prodotti di elettronica, mentre il prodotto A002 (Abbigliamento) ottiene €50 media dei prezzi dei prodotti di abbigliamento .

Nota

Le subquery correlate vengono eseguite per ogni riga della tabella esterna. Per grandi dataset, considerare l'uso di JOIN per migliorare le prestazioni. Assicurarsi che la subquery restituisca al più una riga o utilizzare funzioni aggregate.

DML AVANZATO

DELETE con JOIN

Descrizione

Rimuove righe da una tabella utilizzando condizioni basate su altre tabelle correlate. Utile per eliminare dati orfani, rimuovere riga obsoleti o cancellare elementi associati a determinate condizioni in tabelle collegate.

Sintassi

DELETE FROM tabella1 FROM tabella1 INNER JOIN tabella2 ON tabella1.chiave = tabella2.chiave WHERE condizione;

Esempio Pratico

DELETE articoli

FROM articoli

INNER JOIN categorie ON articoli.categoria_id = categorie.id

WHERE categorie.nome IN ('Obsoleta', 'Discontinua')

AND articoli.quantita_venduta = 0;
Prima
codice nome categoria quantita_venduta categoria.stato
B001 Articolo B1 Elettronica 0 Obsoleta
B002 Articolo B2 Elettronica 150 Obsoleta
B003 Articolo B3 Mobili 0 Discontinua
B004 Articolo B4 Elettronica 75 Attiva
Righe che soddisfano la condizione DELETE (categoria Obsoleta/Discontinua E quantita_venduta = 0)
⬇️
Dopo
codice nome categoria quantita_venduta categoria.stato
B002 Articolo B2 Elettronica 150 Obsoleta
B004 Articolo B4 Elettronica 75 Attiva
2 righe eliminate

Analisi del Codice

Cancella gli articoli che appartiene alla categoria 'Obsoleta' o 'Discontinua': DELETE articoli FROM articoli INNER JOIN categorie ON articoli.categoria_id = categorie.id: La sintassi specifica richiede la tabella da eliminare sia nella DELETE iniziale che come fonte nella FROM, seguita dalla JOIN. Questa duplicazione distingue la tabella target dalle tabelle utilizzate solo per le condizioni.

WHERE categorie.nome IN ('Obsoleta', 'Discontinua') AND articoli.quantita_venduta = 0: Il filtro applica due condizioni cumulative: la categoria deve essere una di quelle obsolete/discontinue E la quantità venduta deve essere zero. Solo le righe che soddisfano entrambe le condizioni vengono eliminate.

Risultato: L'articolo B001 (Obsoleta, 0 vendite) e B003 (Discontinua, 0 vendite) vengono eliminati. B002 rimane pur essendo Obsoleta (ha vendite), B004 rimane perché la sua categoria è Attiva.

Nota

La clausola DELETE con JOIN è supportata da SQL Server e PostgreSQL. MySQL richiede l'eliminazione della tabella principale dalla clausola DELETE iniziale. È consigliabile eseguire prima una SELECT per verificare le righe che verranno eliminate.

🔐 DCL - Data Control Language

Il Data Control Language (DCL) è il sottoinsieme del linguaggio SQL dedicato alla gestione dei permessi e dei diritti di accesso ai dati. In un ambiente di database multiutente, la sicurezza e il controllo degli accessi sono aspetti fondamentali: il DCL permette agli amministratori di definire chi può accedere a quali dati e quali operazioni può eseguire su di essi. Questa sezione del manuale descrive in dettaglio i comandi GRANT, REVOKE, SHOW GRANTS e la gestione dei ruoli, fornendo esempi pratici e spiegazioni approfondite per ogni costrutto.

DCL

GRANT

Descrizione

Il comando GRANT è lo strumento fondamentale per assegnare privilegi a utenti o ruoli su oggetti del database. Un "privilegio" rappresenta il permesso di eseguire una determinata operazione, come leggere dati (SELECT), modificarli (UPDATE, INSERT, DELETE) o modificare la struttura del database stesso (CREATE, ALTER, DROP). Senza privilegi espliciti, un utente non può interagire con il database in alcun modo significativo: il principio di "default deny" garantisce che l'accesso sia sempre negato a meno che non venga esplicitamente concesso. GRANT opera quindi come una "chiave" che apre le porte dell'accesso controllato ai dati, permettendo agli amministratori di implementare il principio del minimo privilegio, secondo il quale ogni utente deve ricevere esattamente i permessi necessari per svolgere le proprie mansioni, nulla di più.

Sintassi

GRANT privilegio [, privilegio ...] ON [database.]oggetto TO 'utente'@'host' [WITH GRANT OPTION] [IDENTIFIED BY 'password'];

Parametri

  • privilegio: Il tipo di permesso da assegnare (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ecc.). È possibile specificare più privilegi separandoli con virgole.
  • database.oggetto: L'ambito del privilegio. Può essere un database specifico (es. azienda.*), una tabella specifica (es. azienda.dipendenti), oppure un oggetto particolare come una vista o una stored procedure.
  • 'utente'@'host': L'identificativo dell'utente destinatario del privilegio, dove "utente" è il nome dell'account e "host" è il nome del computer o l'indirizzo IP da cui l'utente può connettersi.
  • WITH GRANT OPTION: Clausola opzionale che permette all'utente di trasferire i propri privilegi ad altri utenti.
  • IDENTIFIED BY 'password': Clausola opzionale che imposta o modifica la password dell'utente durante l'assegnazione del privilegio.

Esempio 1: Assegnazione di permessi standard

GRANT SELECT, INSERT, UPDATE
ON azienda.dipendenti
TO 'gestore'@'localhost'
IDENTIFIED BY 'password123';
🔍 Spiegazione dettagliata

Questo comando rappresenta un'operazione completa di gestione utente e permessi. L'esecuzione di questo statement produce tre effetti distinti ma interconnessi: innanzitutto, viene creato l'utente 'gestore'@'localhost' se non esiste già nel sistema; successivamente, vengono assegnati tre permessi specifici sulla tabella dipendenti del database azienda, ovvero la possibilità di leggere i dati (SELECT), inserire nuovi riga (INSERT) e modificare riga esistenti (UPDATE); infine, viene impostata la password di autenticazione per l'accesso. È importante notare che il permesso DELETE non è stato incluso, quindi l'utente gestore non potrà cancellare riga dalla tabella, una scelta che riflette tipiche esigenze operative dove chi gestisce i dati deve poterli modificare ma non eliminare definitivamente.

🔍 Focus: Comprendere la sintassi 'utente'@'host'

La clausola TO 'utente'@'host' rappresenta uno degli aspetti più importanti della sicurezza in MySQL e non deve essere sottovalutata. Si tratta di un'identità di accesso completa che comprende due componenti distinte ma complementari. La prima componente, l'utente, è semplicemente il nome dell'account che verrà utilizzato durante la connessione al database; la seconda componente, l'host, specifica da quale postazione l'utente è autorizzato a connettersi. Questa distinzione è fondamentale per la sicurezza perché permette di creare lo stesso nome utente (ad esempio 'admin') con permessi diversi a seconda della provenienza della connessione. Un utente 'admin'@'localhost' potrebbe avere tutti i privilegi di amministrazione, mentre 'admin'@'192.168.1.100' potrebbe avere permessi limitati, creando un sistema di sicurezza geograficamente e logisticamente granulare.

I valori comuni per l'host includono:

  • 'localhost': L'utente può connettersi solo dal server stesso dove è installato il database. Questa è la configurazione più sicura per utenti amministrativi, poiché richiede accesso fisico o remoto al server.
  • '192.168.1.%': L'utente può connettersi da qualsiasi computer nella rete locale 192.168.1.x. Il carattere % funziona come jolly e rappresenta "qualsiasi valore".
  • '%.esempio.com': L'utente può connettersi da qualsiasi computer con dominio *.esempio.com, utile in ambienti con DNS configurato.
  • '%': L'utente può connettersi da qualsiasi computer su Internet. Questa configurazione è estremamente pericolosa e dovrebbe essere utilizzata solo con password forti e permessi limitatissimi, se proprio necessaria.

Esempio 2: Delega dei permessi con GRANT OPTION

GRANT SELECT, INSERT
ON azienda.ordini
TO 'responsabile'@'localhost'
WITH GRANT OPTION;
🔍 Spiegazione di WITH GRANT OPTION

La clausola WITH GRANT OPTION trasforma un utente ordinario in un "mini-amministratore" per quanto riguarda i permessi specificati. Per comprendere il meccanismo, è utile fare un'analogia concreta: senza questa opzione, assegnare un permesso a un utente equivale a dargli le chiavi di una stanza; l'utente può entrare e usare la stanza, ma non può fare copie delle chiavi per altri. Con WITH GRANT OPTION, l'utente riceve non solo le chiavi, ma anche il permesso di andare dal fabbro e farne delle copie per chi ritiene opportuno. Questo significa che 'responsabile' potrà eseguire comandi GRANT simili a quello sopra per assegnare i permessi SELECT e INSERT su azienda.ordini ad altri utenti, creando così una struttura gerarchica di delegazione dei permessi.

⚠️ Attenzione: Effetto a cascata nella revoca

Un aspetto critico da considerare quando si utilizza WITH GRANT OPTION è il comportamento del sistema quando si revoca un privilegio. In MySQL e MariaDB moderni, se l'utente 'responsabile' ha a sua volta concesso permessi ad altri utenti (ad esempio a 'luigi'), e successivamente voi revocate il privilegio a 'responsabile', tutti i privilegi derivati concessi da 'responsabile' vengono automaticamente revocati a cascata. Questo meccanismo garantisce che non esistano "permessi orfani" nel sistema, ma può avere conseguenze impreviste se non si pianifica attentamente la struttura dei permessi. È sempre consigliabile documentare la gerarchia dei permessi concessi con GRANT OPTION e verificare con SHOW GRANTS prima di effettuare operazioni di revoca su utenti che hanno delegato permessi ad altri.

Esempio 3: Permessi globali su tutti i database

GRANT SELECT, SHOW VIEW
ON *.*
TO 'lettore'@'%';
🔍 Spiegazione dei permessi globali

L'oggetto *.* nel comando GRANT indica che i permessi si applicano a tutti i database e tutte le tabelle del server. Il permesso SELECT permette di leggere dati da qualsiasi tabella, mentre SHOW VIEW consente di visualizzare la definizione delle viste. Questo tipo di configurazione è tipica per utenti che devono eseguire query di analisi o reportistica su più database, come un analista dati che necessita di accesso in sola lettura all'intero patrimonio informativo dell'azienda. La restrizione a solo SELECT e SHOW VIEW garantisce che l'utente non possa modificare accidentalmente o deliberatamente i dati, una misura di sicurezza essenziale per ambienti analitici.

Privilegi comuni disponibili

I privilegi in MySQL sono organizzati in livelli e categorie. I privilegi a livello di tabella includono SELECT (lettura), INSERT (inserimento), UPDATE (modifica), DELETE (cancellazione), CREATE (creazione), ALTER (modifica struttura), INDEX (gestione indici), DROP (eliminazione) e LOCK TABLES (blocco tabelle). I privilegi a livello di database aggiungono CREATE TEMPORARY TABLES e EXECUTE per le stored procedure. I privilegi globali includono GRANT OPTION (delega), SUPER (operazioni amministrative), PROCESS (visualizzazione processi), FILE (lettura/scrittura file) e SHOW DATABASES (visualizzazione lista database). Per la sicurezza, è sempre consigliabile assegnare il privilegio minimo necessario per svolgere le funzioni richieste.

DCL

REVOKE

Descrizione

Il comando REVOKE è l'operazione complementare a GRANT e serve per rimuovere privilegi precedentemente assegnati a un utente o ruolo. La gestione dei permessi non è un'operazione statica: gli utenti cambiano ruoli aziendali, cambiano dipartimenti o lasciano l'azienda, e in tutti questi casi è necessario poter rimuovere o modificare i loro accessi. REVOKE agisce come una "serratura" che chiude le porte precedentemente aperte, implementando il principio del minimo privilegio nel tempo. Un amministratore deve essere sempre consapevole dei permessi attivi nel sistema e pronto a revocarli quando non sono più necessari, mantenendo così una postura di sicurezza attiva e dinamica.

Sintassi

REVOKE [GRANT OPTION FOR] privilegio [, privilegio ...] ON [database.]oggetto FROM 'utente'@'host';

Parametri

  • GRANT OPTION FOR: Parola chiave opzionale che revoca specificamente la capacità di delegare permessi, senza togliere i permessi stessi.
  • privilegio: Il tipo di permesso da rimuovere. Se non specificato con GRANT OPTION FOR, il permesso viene rimosso completamente.
  • database.oggetto: L'ambito del privilegio da revocare, deve corrispondere esattamente a quello con cui è stato concesso.
  • 'utente'@'host': L'identificativo dell'utente da cui revocare il privilegio.

Esempio 1: Revoca parziale di permessi specifici

REVOKE DELETE, DROP
ON azienda.dipendenti
FROM 'gestore'@'localhost';
🔍 Spiegazione dettagliata

Questo comando esegue una "chirurgia" precisa sui permessi dell'utente gestore. Anziché rimuovere tutti i privilegi (operazione che potrebbe essere eccessiva o dannosa), vengono rimossi solo due permessi specifici: DELETE (cancellazione di righe) e DROP (eliminazione della tabella stessa). Questo scenario potrebbe verificarsi quando, ad esempio, l'azienda decide che i gestori non devono più poter cancellare dati dei dipendenti per ragioni di conformità normativa, o quando si vuole impedire l'eliminazione accidentale della tabella. L'utente mantiene tutti gli altri permessi che aveva, come SELECT, INSERT e UPDATE, garantendo la continuità operativa mentre si riduce il profilo di rischio.

Esempio 2: Revoca totale dei privilegi

REVOKE ALL PRIVILEGES, GRANT OPTION
ON *.*
FROM 'vecchio_impiegato'@'localhost';
🔍 Spiegazione della revoca totale

Il comando REVOKE ALL PRIVILEGES è lo strumento più drastico per rimuovere l'accesso a un utente. L'uso di *.* come oggetto indica che si intende rimuovere permessi su tutti i database e tutte le tabelle del server. La combinazione ALL PRIVILEGES con GRANT OPTION assicura che vengano rimossi sia i permessi operativi (SELECT, INSERT, ecc.) sia il permesso speciale di delegare questi permessi ad altri. Dopo l'esecuzione di questo comando, 'vecchio_impiegato' non avrà più alcun privilegio nel database, rendendo l'account essenzialmente inutile per operazioni sui dati. Questo tipo di comando è tipicamente utilizzato quando un dipendente lascia l'azienda o cambia ruolo radicalmente, oppure quando si vuole "resettare" completamente i permessi di un utente per ricostruirli da zero in modo controllato.

Esempio 3: Revoca selettiva della GRANT OPTION

REVOKE GRANT OPTION FOR DELETE
ON azienda.ordini
FROM 'supervisore'@'localhost';
🔍 Spiegazione di REVOKE GRANT OPTION FOR

Questa forma avanzata di REVOKE merita particolare attenzione perché permette di rimuovere la capacità di delegare un permesso specifico senza rimuovere il permesso stesso. In questo esempio, il supervisore perde la possibilità di concedere il permesso DELETE ad altri utenti, ma mantiene la capacità di cancellare riga personalmente. Questo scenario è utile quando si vuole permettere a un utente di svolgere il proprio lavoro ma si teme che possa delegare impropriamente permessi pericolosi ad altri. La distinzione tra "poter fare" e "poter far fare" è sottile ma importante nella gestione avanzata della sicurezza dei database.

⚠️ Best practice: Principio del minimo privilegio

La sicurezza di un database si costruisce su un principio fondamentale: ogni utente dovrebbe avere esattamente i permessi necessari per svolgere le proprie funzioni, né più né meno. Questo approccio, noto come "principio del minimo privilegio" (Principle of Least Privilege o PoLP), minimizza i rischi in caso di compromissione di un account, furto di credenziali o errore umano. È buona pratica effettuare periodicamente revisioni dei permessi assegnati, utilizzando SHOW GRANTS per verificare chi ha accesso a cosa, rimuovere tempestivamente i permessi di utenti che hanno cambiato ruolo o lasciato l'azienda, documentare la logica dietro ogni assegnazione di permessi e preferire l'uso dei ruoli per gestire gruppi di permessi in modo coerente. La sicurezza del database non è un evento ma un processo continuo di verifica e aggiustamento.

DCL

SHOW GRANTS

Descrizione

Il comando SHOW GRANTS è lo strumento diagnostico fondamentale per visualizzare i privilegi assegnati a un utente specifico o all'utente corrente. A differenza di GRANT e REVOKE che modificano i permessi, SHOW GRANTS è un comando di sola lettura che fornisce trasparenza sullo stato attuale della sicurezza del database. Questo comando è indispensabile durante le attività di audit di sicurezza, durante il debugging di problemi di accesso (quando un utente segnala di non poter fare qualcosa che dovrebbe poter fare), durante la migrazione di database tra server (per replicare esattamente i permessi) e durante la formazione di nuovi amministratori che devono comprendere la struttura dei permessi esistente. L'output di SHOW GRANTS non è una tabella di dati ma una serie di statement SQL che, se eseguiti, ricreerebbero esattamente quei permessi su un altro server.

Sintassi

SHOW GRANTS [FOR 'utente'@'host'];

Parametri

  • FOR 'utente'@'host': Clausola opzionale che specifica per quale utente visualizzare i permessi. Se omessa, mostra i permessi dell'utente corrente (quello con cui si è connessi).

Esempio 1: Visualizzazione dei permessi di un utente specifico

SHOW GRANTS FOR 'gestore'@'localhost';

Esempio 2: Visualizzazione dei propri permessi

SHOW GRANTS;
🔍 Interpretazione dell'output

L'output di SHOW GRANTS è particolarmente utile perché mostra i permessi nella forma di comandi SQL eseguibili. Un tipico output potrebbe apparire come:

-- Output esempio:
GRANT SELECT, INSERT, UPDATE ON `azienda`.`dipendenti` TO 'gestore'@'localhost'
GRANT SELECT ON `azienda`.`report` TO 'gestore'@'localhost'

Questa rappresentazione ha diversi vantaggi pratici: primo, è immediatamente leggibile e comprensibile anche per chi non è esperto di tabelle di sistema; secondo, può essere copiata e incollata su un altro server per replicare esattamente gli stessi permessi (utile per ambienti di sviluppo, test e produzione); terzo, può essere salvata come parte della documentazione del database o inclusa in script di deployment. Quando si deve migrare un database o creare un ambiente di test identico a quello di produzione, SHOW GRANTS è il comando che fornisce la "ricetta" esatta dei permessi da ricreare.

Utilizzo pratico in scenari reali

SHOW GRANTS trova applicazione in molte situazioni operative quotidiane. Durante il debugging di problemi di accesso, se un utente segnala di ricevere un errore "Access denied", SHOW GRANTS può rivelare immediatamente se i permessi necessari sono effettivamente assegnati o se c'è un errore di digitazione nell'account utente (ad esempio 'utente'@'%' invece di 'utente'@'localhost'). Durante la preparazione di ambienti di test, si può usare SHOW GRANTS sull'ambiente di produzione per generare gli statement da eseguire sul test. Durante gli audit di sicurezza periodici, SHOW GRANTS permette di verificare che nessun permesso inappropriato sia stato accidentalmente concesso. Durante la rotazione del personale, SHOW GRANTS aiuta a documentare cosa aveva accesso un dipendente prima della sua uscita dall'azienda.

DCL

Gestione dei Ruoli (Roles)

Descrizione

Il sistema dei ruoli rappresenta un'evoluzione fondamentale nella gestione dei permessi SQL, introducendo un livello di astrazione tra i permessi individuali e gli utenti. Un ruolo può essere pensato come un "contenitore" o un "profilo" di permessi che può essere assegnato a più utenti. Invece di assegnare manualmente gli stessi permessi a dieci contabili uno per uno, si crea un ruolo 'contabile' con tutti i permessi necessari, e poi si assegna semplicemente quel ruolo ai dieci utenti. Questo approccio centralizza la gestione dei permessi, riduce drasticamente il rischio di errori e semplifica enormemente la manutenzione nel tempo. Quando serve modificare i permessi per tutti i contabili, si modifica il ruolo una sola volta e la modifica si propaga automaticamente a tutti gli utenti che lo detengono.

Sintassi fondamentale

-- Creazione di un ruolo CREATE ROLE 'nome_ruolo'; -- Assegnazione di permessi al ruolo GRANT privilegio [, privilegio ...] ON [database.]oggetto TO 'nome_ruolo'; -- Attivazione del ruolo per l'utente corrente SET ROLE 'nome_ruolo'; -- Assegnazione del ruolo a un utente (versione permanente) GRANT 'nome_ruolo' TO 'utente'@'host'; -- Visualizzazione dei ruoli attivi SELECT CURRENT_ROLES();

Componenti del sistema di ruoli

  • CREATE ROLE: Crea un nuovo ruolo nel database. Il nome del ruolo segue le stesse convenzioni degli utenti e può includere il pattern 'nome'@'host' per ruoli specifici di un particolare host.
  • GRANT ... TO 'ruolo': Assegna permessi a un ruolo esattamente come si farebbe per un utente. Un ruolo può contenere qualsiasi combinazione di permessi.
  • GRANT 'ruolo' TO 'utente': Assegna un ruolo a un utente. L'utente "eredita" tutti i permessi contenuti nel ruolo.
  • SET ROLE: Attiva un ruolo per la sessione corrente. Alcuni sistemi richiedono l'attivazione esplicita, altri possono essere configurati per attivare automaticamente i ruoli all'accesso.
  • DROP ROLE: Rimuove un ruolo dal sistema. I permessi vengono automaticamente rimossi da tutti gli utenti che lo possedevano.

Esempio completo: Creazione del ruolo "Contabile"

-- Fase 1: Creazione del ruolo
CREATE ROLE 'contabile';

-- Fase 2: Assegnazione permessi al ruolo
GRANT SELECT, INSERT, UPDATE ON azienda.fatture TO 'contabile';
GRANT SELECT ON azienda.clienti TO 'contabile';
GRANT SELECT ON azienda.prodotti TO 'contabile';

-- Fase 3: Assegnazione utenti al ruolo
GRANT 'contabile' TO 'mario'@'localhost';
GRANT 'contabile' TO 'luigi'@'localhost';
GRANT 'contabile' TO 'paola'@'localhost';
🔍 Spiegazione dettagliata delle fasi

L'esempio mostra il processo completo di creazione e popolamento di un ruolo. Nella Fase 1, viene semplicemente creato un "contenitore vuoto" chiamato 'contabile'. Questo ruolo non ha ancora nessun permesso, è solo un nome che potrà essere assegnato agli utenti. Nella Fase 2, si "riempie" il ruolo con i permessi necessari: i contabili possono leggere, inserire e modificare fatture, ma possono solo leggere dati di clienti e prodotti. Notare che i permessi vengono assegnati al ruolo con la sintassi GRANT ... TO 'contabile', dove 'contabile' è trattato come un "utente speciale" che non si connette ma detiene permessi. Nella Fase 3, si assegna il ruolo completato agli utenti individuali. Ogni utente che riceve il ruolo 'contabile' ottiene automaticamente tutti i permessi definiti nel ruolo, senza bisogno di ripetere ogni singolo GRANT.

Esempio: Modifica dei permessi del ruolo

-- Aggiunta di un nuovo permesso al ruolo
GRANT DELETE ON azienda.fatture TO 'contabile';

-- Verifica: tutti i contabili hanno ora il permesso DELETE
SHOW GRANTS FOR 'mario'@'localhost';
🔍 Il potere della centralizzazione

Questo esempio dimostra il vantaggio principale dei ruoli: la modificazione centralizzata. Se l'azienda decide che i contabili devono poter eliminare fatture vecchie o errate, basta eseguire un singolo GRANT al ruolo 'contabile'. Immediatamente, Mario, Luigi, Paola e qualsiasi altro utente a cui è stato assegnato il ruolo ottengono il permesso DELETE sulle fatture. Non c'è bisogno di modificare gli account individuali, non c'è rischio di dimenticare qualcuno, non c'è bisogno di ripetere la stessa operazione dieci volte. Questo principio si applica anche alla revoca: se domani si decide di togliere il permesso DELETE, si esegue REVOKE sul ruolo e la modifica si propaga a tutti.

Esempio: Gestione di ruoli multipli per un utente

-- Creazione di ruoli aggiuntivi
CREATE ROLE 'lettore_report';
GRANT SELECT ON azienda.report_annuali TO 'lettore_report';

-- Assegnazione di ruoli multipli allo stesso utente
GRANT 'contabile' TO 'mario'@'localhost';
GRANT 'lettore_report' TO 'mario'@'localhost';

-- Mario ora ha i permessi di entrambi i ruoli
SET ROLE 'contabile', 'lettore_report';
🔍 Combinazione di ruoli

Un utente può essere assegnato a più ruoli, ottenendo l'unione di tutti i permessi contenuti in ciascuno di essi. Mario, che è un contabile ma deve anche accedere ai report annuali, riceve sia il ruolo 'contabile' che il ruolo 'lettore_report'. I suoi permessi totali saranno la somma dei permessi di entrambi i ruoli. La clausola SET ROLE permette di attivare ruoli specifici per la sessione corrente, utile quando un utente ha ruoli multipli ma non vuole averli tutti attivi contemporaneamente (ad esempio, per limitare temporaneamente i propri permessi durante operazioni delicate). Alcuni database supportano anche ruoli obbligatori che vengono attivati automaticamente all'accesso, o ruoli predefiniti che si attivano di default senza bisogno di SET ROLE esplicito.

Vantaggi strategici dell'utilizzo dei ruoli

L'adozione sistematica dei ruoli porta benefici significativi alla gestione del database. Il primo vantaggio è la riduzione della complessità amministrativa: invece di gestire centinaia di combinazioni di permessi per centinaia di utenti, si gestiscono una dozzina di ruoli ben definiti. Il secondo vantaggio è la coerenza: tutti gli utenti con lo stesso ruolo hanno esattamente gli stessi permessi, eliminando discrepanze accidentali. Il terzo vantaggio è la tracciabilità: quando si deve capire chi può fare cosa, basta guardare i ruoli invece di analizzare ogni singolo account. Il quarto vantaggio è la velocità di onboarding: quando arriva un nuovo dipendente, basta assegnargli i ruoli appropriati invece di costruire manualmente una lista di permessi. Il quinto vantaggio è la facilità di audit: per verificare la sicurezza del database, basta controllare che i ruoli siano configurati correttamente, una verifica molto più semplice che controllare migliaia di assegnazioni individuali.

⚠️ Considerazioni sulla migrazione ai ruoli

L'introduzione dei ruoli in un database esistente richiede una pianificazione attenta. È consigliabile mappare prima tutti i permessi esistenti degli utenti (usando SHOW GRANTS), identificare gruppi naturali di permessi che corrispondono a funzioni aziendali, creare i ruoli corrispondenti, verificare che i permessi del ruolo siano identici a quelli degli utenti che lo riceveranno, assegnare i ruoli agli utenti, rimuovere i permessi individuali duplicati (per evitare confusione) e documentare la nuova struttura. Durante la migrazione, è importante non rimuovere mai permessi prima di aver verificato che i ruoli li sostituiscano completamente. Un approccio graduale, ruolo per ruolo, è preferibile a una migrazione massiva che potrebbe introdurre errori difficili da tracciare.

Riepilogo del Data Control Language

Il DCL rappresenta il cuore della sicurezza in un database relazionale, permettendo di controllare con precisione chi può accedere ai dati e quali operazioni può eseguire. GRANT apre le porte assegnando permessi, REVOKE le chiude rimuovendoli, SHOW GRANTS mostra lo stato attuale dei permessi e i ruoli permettono di gestire permessi in modo centralizzato e scalabile. Una buona pratica di sicurezza richiede di assegnare sempre il minimo privilegio necessario, verificare periodicamente i permessi concessi, utilizzare i ruoli per semplificare la gestione di gruppi di utenti e documentare ogni decisione di sicurezza. La sicurezza del database non è un prodotto ma un processo continuo che richiede attenzione e manutenzione costante.

🔄 TCL - Transaction Control Language

Cos'è il TCL?

Il Transaction Control Language è un sottoinsieme di SQL dedicato alla gestione delle transazioni. Una transazione è un insieme di operazioni SQL che vengono eseguite in blocco: o tutte le operazioni hanno successo, oppure nessuna di esse viene applicata al database.

Le transazioni garantiscono le proprietà ACID:

  • Atomicità: tutte le operazioni di una transazione vengono completate o nessuna
  • Consistenza: il database passa da uno stato valido a un altro stato valido
  • Isolamento: le transazioni concorrenti non interferiscono tra loro
  • Durabilità: una volta confermata, la transazione è permanente
TCL

START TRANSACTION / BEGIN

Descrizione

Inizia una nuova transazione esplicita. Da questo momento, tutte le operazioni SQL successive (INSERT, UPDATE, DELETE) fanno parte di un'unica unità logica indivisibile. Le modifiche non saranno visibili agli altri utenti fino al COMMIT e potranno essere annullate con ROLLBACK.

Senza una transazione esplicita, la maggior parte dei DBMS opera in modalità autocommit: ogni singola istruzione viene automaticamente confermata. Usando START TRANSACTION, si disabilita temporaneamente l'autocommit per raggruppare più operazioni.

Sintassi

START TRANSACTION; -- oppure (sintassi alternativa supportata da MySQL, PostgreSQL) BEGIN; -- oppure (SQL Server) BEGIN TRANSACTION;

Esempio 1: Prenotazione Hotel

START TRANSACTION;

-- Segna la camera come non disponibile
UPDATE camere SET disponibile = 0 WHERE id = 101;

-- Crea la prenotazione
INSERT INTO prenotazioni (cliente, camera, data_inizio, data_fine)
VALUES ('Mario Rossi', 101, '2026-07-01', '2026-07-05');

-- Addebita il pagamento
UPDATE clienti SET saldo = saldo - 400.00 WHERE nome = 'Mario Rossi';

-- Se tutto è andato bene:
COMMIT;

-- Se c'è stato un errore in qualsiasi punto:
-- ROLLBACK;

Esempio 2: Trasferimento Bancario

BEGIN;

-- Preleva dal conto origine
UPDATE conti SET saldo = saldo - 1000 
WHERE numero_conto = 'IT001';

-- Accredita sul conto destinazione
UPDATE conti SET saldo = saldo + 1000 
WHERE numero_conto = 'IT002';

-- Registra il movimento
INSERT INTO movimenti (da_conto, a_conto, importo, data_ora)
VALUES ('IT001', 'IT002', 1000, NOW());

COMMIT;
Perché usare le transazioni?

Senza la transazione, se il sistema si bloccasse dopo il prelievo ma prima dell'accredito, i soldi andrebbero "persi". Con la transazione, o entrambe le operazioni avvengono, o nessuna delle due.

TCL

COMMIT

Descrizione

COMMIT conferma definitivamente tutte le modifiche effettuate durante la transazione corrente, rendendole permanenti nel database. Una volta eseguito il commit:

  • Le modifiche diventano visibili a tutti gli altri utenti e sessioni
  • I dati vengono scritti in modo persistente sul disco
  • Non è più possibile annullare le operazioni con ROLLBACK
  • I lock sulle risorse vengono rilasciati
  • La transazione termina e ne inizia implicitamente una nuova

Sintassi

COMMIT; -- oppure (forma estesa) COMMIT WORK;

Esempio: Conferma Ordine E-commerce

START TRANSACTION;

-- Crea l'ordine
INSERT INTO ordini (cliente_id, data_ordine, totale)
VALUES (42, NOW(), 159.99);

-- Ottieni l'ID dell'ordine appena creato
SET @ordine_id = LAST_INSERT_ID();

-- Inserisci i prodotti dell'ordine
INSERT INTO ordini_dettagli (ordine_id, prodotto_id, quantita, prezzo)
VALUES (@ordine_id, 101, 2, 49.99),
       (@ordine_id, 205, 1, 59.99);

-- Aggiorna le giacenze di magazzino
UPDATE prodotti SET giacenza = giacenza - 2 WHERE id = 101;
UPDATE prodotti SET giacenza = giacenza - 1 WHERE id = 205;

-- Tutto OK: conferma definitivamente
COMMIT;
Atomicità garantita

Con COMMIT, l'intero ordine (testata, dettagli e aggiornamento magazzino) viene salvato insieme. Se non si esegue il commit, le modifiche rimangono in sospeso e potrebbero essere perse in caso di disconnessione o crash.

Attenzione

Alcune istruzioni DDL (come CREATE TABLE, DROP TABLE, ALTER TABLE) causano un commit implicito in molti DBMS. Questo significa che la transazione viene automaticamente confermata prima e dopo l'esecuzione di tali comandi.

TCL

ROLLBACK

Descrizione

ROLLBACK annulla tutte le modifiche effettuate dall'inizio della transazione corrente, riportando il database allo stato in cui si trovava prima del START TRANSACTION. È l'operazione opposta al COMMIT.

Situazioni tipiche in cui si usa ROLLBACK:

  • Si verifica un errore durante l'elaborazione
  • Una condizione di business non è soddisfatta
  • L'utente annulla l'operazione
  • Si rileva un'inconsistenza nei dati
  • Un vincolo di integrità viene violato

Sintassi

ROLLBACK; -- oppure (forma estesa) ROLLBACK WORK;

Esempio: Gestione Errori in un Trasferimento

START TRANSACTION;

-- Verifica saldo sufficiente
SELECT saldo INTO @saldo_attuale 
FROM conti WHERE numero_conto = 'IT001';

-- Preleva dal conto origine
UPDATE conti SET saldo = saldo - 5000 
WHERE numero_conto = 'IT001';

-- Controlla se il saldo è andato in negativo
SELECT saldo INTO @nuovo_saldo 
FROM conti WHERE numero_conto = 'IT001';

-- Se il saldo è negativo, annulla tutto
IF @nuovo_saldo < 0 THEN
    ROLLBACK;
    SELECT 'Errore: saldo insufficiente' AS messaggio;
ELSE
    UPDATE conti SET saldo = saldo + 5000 
    WHERE numero_conto = 'IT002';
    COMMIT;
    SELECT 'Trasferimento completato' AS messaggio;
END IF;

Esempio: Rollback Automatico su Errore

-- In MySQL con stored procedure
DELIMITER //
CREATE PROCEDURE trasferisci_fondi(
    IN p_da VARCHAR(20),
    IN p_a VARCHAR(20),
    IN p_importo DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Errore: transazione annullata' AS risultato;
    END;

    START TRANSACTION;
    
    UPDATE conti SET saldo = saldo - p_importo WHERE numero = p_da;
    UPDATE conti SET saldo = saldo + p_importo WHERE numero = p_a;
    
    COMMIT;
    SELECT 'Trasferimento riuscito' AS risultato;
END //
DELIMITER ;
Importante

Dopo un ROLLBACK, non è possibile recuperare le modifiche annullate. Il rollback è un'operazione definitiva che riporta i dati esattamente allo stato precedente all'inizio della transazione.

Rollback implicito

Se una sessione viene chiusa (disconnessione, timeout, crash) senza un COMMIT esplicito, il DBMS esegue automaticamente un ROLLBACK per garantire la consistenza dei dati.

TCL

SAVEPOINT

Descrizione

SAVEPOINT crea un punto di salvataggio all'interno di una transazione. Questo permette di eseguire un rollback parziale, annullando solo le operazioni successive al savepoint senza perdere quelle precedenti.

I savepoint sono utili quando:

  • Si eseguono operazioni complesse in più fasi
  • Si vuole poter annullare solo una parte della transazione
  • Si implementano logiche di retry per singole operazioni
  • Si gestiscono operazioni opzionali che potrebbero fallire

È possibile creare più savepoint nella stessa transazione, ciascuno con un nome univoco.

Sintassi

-- Crea un punto di salvataggio SAVEPOINT nome_savepoint; -- Torna a un punto di salvataggio (rollback parziale) ROLLBACK TO SAVEPOINT nome_savepoint; -- oppure ROLLBACK TO nome_savepoint; -- Elimina un punto di salvataggio RELEASE SAVEPOINT nome_savepoint;

Esempio 1: Ordine con Dettagli Opzionali

START TRANSACTION;

-- Fase 1: Crea l'ordine principale (obbligatorio)
INSERT INTO ordini (cliente_id, data_ordine, stato)
VALUES (1, NOW(), 'nuovo');
SET @ordine_id = LAST_INSERT_ID();

-- Savepoint dopo la creazione dell'ordine
SAVEPOINT ordine_creato;

-- Fase 2: Prova ad aggiungere i dettagli
INSERT INTO ordini_dettagli (ordine_id, prodotto_id, quantita)
VALUES (@ordine_id, 101, 5);

-- Savepoint dopo i dettagli
SAVEPOINT dettagli_aggiunti;

-- Fase 3: Prova ad applicare uno sconto (potrebbe fallire)
INSERT INTO sconti_applicati (ordine_id, codice_sconto, importo)
VALUES (@ordine_id, 'PROMO2026', 15.00);

-- Se lo sconto non è valido, torna ai dettagli ma mantieni l'ordine
-- ROLLBACK TO dettagli_aggiunti;

COMMIT;

Esempio 2: Migrazione Dati con Gestione Errori

START TRANSACTION;

-- Migrazione tabella clienti
INSERT INTO clienti_nuovo SELECT * FROM clienti_vecchio;
SAVEPOINT clienti_migrati;

-- Migrazione tabella ordini
INSERT INTO ordini_nuovo SELECT * FROM ordini_vecchio;
SAVEPOINT ordini_migrati;

-- Migrazione tabella prodotti (potrebbe avere problemi)
INSERT INTO prodotti_nuovo SELECT * FROM prodotti_vecchio;

-- Se la migrazione prodotti fallisce:
-- ROLLBACK TO ordini_migrati;
-- (clienti e ordini rimangono migrati)

COMMIT;

Esempio 3: Rilascio di un Savepoint

START TRANSACTION;

UPDATE inventario SET quantita = quantita - 10 WHERE id = 1;
SAVEPOINT sp1;

UPDATE inventario SET quantita = quantita - 5 WHERE id = 2;
SAVEPOINT sp2;

-- Operazione completata con successo, non serve più sp1
RELEASE SAVEPOINT sp1;

-- Ora sp1 non esiste più, ma sp2 è ancora disponibile

COMMIT;
RELEASE SAVEPOINT

RELEASE SAVEPOINT elimina il savepoint indicato. Non esegue commit né rollback, semplicemente rimuove il punto di salvataggio. I savepoint successivi rimangono validi. Usare RELEASE libera risorse del sistema quando un savepoint non è più necessario.

Comportamento dei Savepoint
  • Un ROLLBACK TO SAVEPOINT annulla solo le operazioni dopo il savepoint
  • Il savepoint rimane attivo dopo il rollback (può essere riutilizzato)
  • Un COMMIT o ROLLBACK completo elimina tutti i savepoint
  • I nomi dei savepoint sono case-sensitive in alcuni DBMS
TCL

SET TRANSACTION

Descrizione

SET TRANSACTION configura le caratteristiche della prossima transazione, in particolare il livello di isolamento. Il livello di isolamento determina come le transazioni concorrenti interagiscono tra loro e quali fenomeni anomali sono permessi.

Sintassi

SET TRANSACTION ISOLATION LEVEL livello; -- Livelli disponibili (dal meno al più restrittivo): -- READ UNCOMMITTED -- READ COMMITTED -- REPEATABLE READ -- SERIALIZABLE

I Livelli di Isolamento

Livello Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Possibile Possibile Possibile
READ COMMITTED Prevenuto Possibile Possibile
REPEATABLE READ Prevenuto Prevenuto Possibile
SERIALIZABLE Prevenuto Prevenuto Prevenuto
Glossario dei Fenomeni
  • Dirty Read: leggere dati modificati da un'altra transazione non ancora confermata
  • Non-Repeatable Read: rileggendo gli stessi dati, si ottengono valori diversi perché un'altra transazione li ha modificati
  • Phantom Read: rieseguendo una query, appaiono o scompaiono righe perché un'altra transazione ha inserito o eliminato riga

Esempio: Report Finanziario Consistente

-- Per un report che richiede dati consistenti
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT SUM(saldo) AS totale_depositi 
FROM conti WHERE tipo = 'deposito';

SELECT SUM(saldo) AS totale_prestiti 
FROM conti WHERE tipo = 'prestito';

-- I dati sono garantiti consistenti tra le due query

COMMIT;
Trade-off Performance vs Consistenza

Livelli di isolamento più alti garantiscono maggiore consistenza ma riducono la concorrenza e le performance. Scegli il livello minimo necessario per le tue esigenze.

🔍 Query Language (QL)

Approfondimento Separato

La Query Language (QL), focalizzata sull’istruzione SELECT e sulle interrogazioni complesse — tra cui JOIN, GROUP BY, HAVING, subquery, funzioni aggregate, finestre analitiche e ottimizzazione delle prestazioni — è trattata in documenti HTML dedicati.

Questa scelta permette:

  • una maggiore gradualità didattica,
  • un focus esclusivo sulla logica delle interrogazioni,
  • l’inclusione di esempi contestuali e quiz interattivi specifici.

➜ Consulta i file separati per approfondire la potenza della SELECT in MySQL/MariaDB.