Corso MariaDB / Modificare colonne e vincoli con ALTER TABLE

ALTER TABLE modifica colonne, tipi e vincoli di una tabella esistente. Verificare che le righe attuali rispettino le nuove regole e separare aggiunta della colonna, compilazione dei dati preesistenti e applicazione dei vincoli, così da riconoscere errori e possibili perdite di informazioni.

Conservare la definizione attuale e preparare il recupero

Una modifica dello schema non consiste soltanto nel cambiare un'etichetta. Restringere un tipo o aggiungere NOT NULL e UNIQUE può fallire sui dati esistenti oppure modificarne i valori. Salvare SHOW CREATE TABLE e un backup adeguato dei dati, individuare le query dipendenti e provare prima in un ambiente separato. La guida ai vincoli di inserimento descrive le regole da applicare.

Modifica Controllo preliminare Rischio
Aggiunta di NOT NULL Numero e significato delle righe NULL Serve una regola per i dati mancanti
Aggiunta di UNIQUE Gruppi di chiavi duplicate Le righe in conflitto impediscono la modifica
Riduzione della lunghezza del testo Massima lunghezza in caratteri Rifiuto o troncamento dei valori
Eliminazione di una colonna Utilizzatori e requisiti di conservazione Eliminazione dei valori memorizzati

Definire risultato atteso e procedura di recupero prima di una modifica che può perdere dati. Il riferimento MariaDB di ALTER TABLE descrive operazioni su colonne, vincoli e opzioni di esecuzione.

Preparare i prodotti esistenti prima di aggiungere un codice

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

Controllare SELECT VERSION(), DATABASE(); e SHOW TABLES; prima di creare products_38_lab. Se l'oggetto esiste, esaminarne prima definizione e righe. Usare soltanto dati di prova eliminabili e conservare le tabelle applicative. Il blocco seguente prepara tutti e sei i prodotti.

USE shopcourse;
CREATE TABLE products_38_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p38_price CHECK (price >= 0),
  CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Il codice mantiene i testi coreani della fonte per conservare l'esercizio UTF-8. I prodotti 1–6 sono quaderno, penna, tazza, lampada, cartellina e cavo; le categorie indicano cancelleria, articoli per la casa ed elettronica. Conservare i testi e usare gli ID nel confronto dei risultati.

L'esercizio aggiunge sku come codice prodotto alle righe esistenti. Inizialmente consentire NULL per compilare i codici prima di renderli obbligatori. Il formato generato serve soltanto all'esempio: per un codice reale definire lunghezza, stabilità e ambito di unicità secondo i requisiti.

Aggiungere una colonna nullable e verificare la compilazione

SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
 COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;

Dopo ADD COLUMN e prima di UPDATE, la nuova colonna contiene NULL nelle sei righe già presenti. UPDATE produce P001–P006. Dopo la compilazione, rows_total, known_sku e unique_sku devono valere tutti sei. Se restano codici mancanti o duplicati, fermarsi prima di applicare i vincoli finali.

Confrontare anche nomi, prezzi, scorte e altri attributi originari con i dati precedenti. Compilare una nuova colonna non dovrebbe modificare altri fatti. WHERE sku IS NULL limita la compilazione ai codici mancanti, ma non sostituisce i controlli sul formato e sull'unicità.

Non presumere che ALTER e l'UPDATE successivo costituiscano una sola transazione interamente annullabile. Le istruzioni di definizione dei dati, o DDL, hanno confini di commit propri. Registrare quali fasi sono riuscite per riprendere o recuperare intenzionalmente una migrazione interrotta.

Applicare i vincoli e provare separatamente i rifiuti

ALTER TABLE products_38_lab
 MODIFY COLUMN sku VARCHAR(20) NOT NULL,
 ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
 ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;

sku diventa obbligatorio e univoco; il vincolo sul nome richiede una lunghezza positiva in caratteri. Con MODIFY confrontare e specificare nuovamente tipo, lunghezza, regola NULL, valore predefinito e altri attributi da conservare. Gli attributi omessi non vengono necessariamente mantenuti automaticamente.

Eseguire l'errore intenzionale seguente separatamente dal file della migrazione normale. L'istruzione tenta di assegnare al prodotto 2 il codice P001 già appartenente al prodotto 1. Attendersi un errore di unicità e rileggere il prodotto 2 per verificare che conservi P002.

UPDATE products_38_lab SET sku='P001' WHERE product_id=2;

Verificare anche la regola del nome senza disattivare i vincoli. CHAR_LENGTH(product_name) > 0 rifiuta una stringa vuota, ma uno spazio ha lunghezza uno e soddisfa la condizione. Se vanno rifiutati anche nomi di soli spazi, definire la pulizia richiesta: il controllo della lunghezza non copre ogni forma di input vuoto.

Se l'aggiunta di UNIQUE fallisce per duplicati preesistenti, esaminare le righe in conflitto e stabilire come correggerle. Non eliminare righe soltanto per far riuscire la migrazione senza sapere quali informazioni vanno conservate.

Valutare blocchi, opzioni online e costo della modifica

La modifica può richiedere copia della tabella o blocchi secondo versione, motore, operazione e dimensioni. ALGORITHM e LOCK non rendono ogni ALTER privo di interruzioni. Verificare le combinazioni supportate, il comportamento in caso di errore e l'impatto delle attese applicative sulla versione effettiva.

Una transazione lunga può mantenere un blocco sui metadati e ritardare ALTER. La durata osservata su una piccola tabella di prova non predice quella in produzione. Considerare dati e indici, spazio su disco, tempo di recupero e necessità di manutenzione o distribuzione graduale compatibile.

Distinguere eliminazione della colonna e ripristino dei dati

Eliminare la colonna appena aggiunta elimina anche i valori accumulati al suo interno. Non equivale necessariamente a ripristinare lo stato precedente. Modifiche potenzialmente distruttive richiedono backup iniziali e una procedura di recupero verificata. Nell'esercizio confrontare i sei nomi, prezzi e valori di scorta originari prima di rimuovere la tabella.

Prima di ripetere, controllare database selezionato, definizione effettiva e righe memorizzate. Salvare ciò che va conservato ed eliminare soltanto products_38_lab di questo articolo. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.products_38_lab;