SQL ALTER TABLE Add Column: la guida completa

Aggiungere una colonna a una tabella esistente in SQL è un'operazione comune che ti capiterà di fare man mano che lo schema del tuo database evolve. L'istruzione ALTER TABLE ti permette di modificare la struttura di una tabella esistente senza perdere dati. Questa guida spiega come usare ALTER TABLE per aggiungere colonne, con esempi pratici.

Capire ALTER TABLE

Il comando ALTER TABLE serve ad aggiungere, eliminare o modificare colonne in una tabella esistente. Si può usare anche per cambiare i vincoli della tabella. Questo tutorial si concentra sull'aggiunta di nuove colonne.

Quando aggiungi una nuova colonna, devi specificarne il nome, il tipo di dato ed eventuali vincoli o valori predefiniti. L'operazione è atomica e non modifica i dati esistenti.

Sintassi di base per aggiungere una colonna

Ecco la sintassi di base per aggiungere una nuova colonna a una tabella:

ALTER TABLE table_name
ADD column_name column_type [constraint];

• table_name: il nome della tabella a cui stai aggiungendo la colonna.
• column_name: il nome della nuova colonna.
• column_type: il tipo di dato della nuova colonna (ad es. VARCHAR, INT, DATE, ecc.).
• constraint: facoltativo. Qualsiasi vincolo aggiuntivo come NOT NULL, UNIQUE o valori DEFAULT.

Esempio: aggiungere una colonna

Partiamo da una semplice tabella employees:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50)
);

-- Current data in employees table:
INSERT INTO employees (id, name, position) VALUES (1, 'Alice', 'Engineer');
INSERT INTO employees (id, name, position) VALUES (2, 'Bob', 'Manager');

SELECT * FROM employees;

Output:

+----+-------+----------+
| id | name  | position |
+----+-------+----------+
|  1 | Alice | Engineer |
|  2 | Bob   | Manager  |
+----+-------+----------+

Aggiungere una nuova colonna per lo stipendio

Aggiungiamo una nuova colonna chiamata salary con tipo di dato DECIMAL(10, 2), che può contenere numeri fino a 10 cifre, di cui 2 decimali:

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

Questo comando modifica la tabella employees aggiungendo la nuova colonna salary. Le righe esistenti avranno valore NULL in questa colonna finché non le aggiorni.

Verificare la struttura della tabella

Verifica che la colonna sia stata aggiunta con DESCRIBE o SHOW COLUMNS (a seconda del tuo database):

DESCRIBE employees;

Output:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(100)| YES  |     | NULL    |       |
| position | varchar(50) | YES  |     | NULL    |       |
| salary   | decimal(10,2)| YES |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Aggiungere una colonna con un valore predefinito

Puoi anche aggiungere una colonna con un valore predefinito. Ecco come aggiungere una colonna hire_date che ha come valore predefinito la data corrente:

ALTER TABLE employees
ADD hire_date DATE DEFAULT CURRENT_DATE;

Le nuove righe inserite nella tabella employees avranno automaticamente hire_date impostato alla data corrente se non viene fornito alcun valore.

Aggiungere una colonna con vincolo NOT NULL

Aggiungere una colonna con vincolo NOT NULL a una tabella esistente richiede qualche attenzione in più. Non puoi semplicemente aggiungere una colonna NOT NULL a una tabella che contiene già dati senza fornire un valore predefinito, perché le righe esistenti violerebbero il vincolo.

Il problema di NOT NULL con dati esistenti

Questo comando fallirà se la tabella contiene già delle righe:

-- This will fail on tables with existing data
ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL;

L'errore si verifica perché le righe esistenti avrebbero valori NULL nella nuova colonna, violando il vincolo NOT NULL.

Soluzione 1: aggiungere la colonna con un valore predefinito

La soluzione più semplice è fornire un valore predefinito:

ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL DEFAULT 'Unknown';

Funziona perché:

1. La colonna viene aggiunta con il valore predefinito per tutte le righe esistenti
2. Il vincolo NOT NULL è subito rispettato
3. I futuri inserimenti devono fornire un valore o usare quello predefinito

Soluzione 2: procedura in più passaggi

Per avere più controllo sui valori, usa un approccio in più passaggi:

-- Step 1: Add the column as nullable
ALTER TABLE employees
ADD phone_number VARCHAR(20);

-- Step 2: Update existing rows with appropriate values
UPDATE employees 
SET phone_number = 'Unknown' 
WHERE phone_number IS NULL;

-- Step 3: Add the NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone_number SET NOT NULL;

Nota: la sintassi del passaggio 3 varia a seconda del database:

• PostgreSQL: ALTER COLUMN phone_number SET NOT NULL
• MySQL: MODIFY COLUMN phone_number VARCHAR(20) NOT NULL
• SQL Server: ALTER COLUMN phone_number VARCHAR(20) NOT NULL

Soluzione 3: valori predefiniti condizionali

Puoi impostare valori predefiniti diversi in base ai dati esistenti:

-- Add column with temporary default
ALTER TABLE employees
ADD department VARCHAR(50) NOT NULL DEFAULT 'TBD';

-- Update with conditional logic based on position
UPDATE employees 
SET department = CASE 
    WHEN position = 'Engineer' THEN 'Engineering'
    WHEN position = 'Manager' THEN 'Management'
    ELSE 'General'
END;

-- Optionally remove the default if you don't want it for future inserts
ALTER TABLE employees
ALTER COLUMN department DROP DEFAULT;

Best practice per le colonne NOT NULL

1. Pianifica in anticipo: valuta i vincoli NOT NULL già in fase di progettazione della tabella
2. Usa valori predefiniti sensati: scegli valori predefiniti coerenti con la tua logica di business
3. Prima testa: prova sempre le operazioni ALTER TABLE su una copia dei tuoi dati
4. Documenta le modifiche: tieni traccia delle modifiche allo schema per comunicarle al team
5. Pensa alla migrazione dei dati: pianifica come popolare la nuova colonna con dati significativi

Verificare i vincoli NOT NULL

Dopo aver aggiunto una colonna NOT NULL, verifica che il vincolo sia attivo:

-- Check table structure
DESCRIBE employees;

-- Verify constraint by attempting to insert NULL (should fail)
INSERT INTO employees (id, name, position, phone_number) 
VALUES (3, 'Carol', 'Analyst', NULL);
-- Error: Column 'phone_number' cannot be null

Operazioni correlate

Ora che sai come aggiungere colonne, potresti voler scoprire anche altre operazioni ALTER TABLE:

• Come rinominare le colonne con ALTER TABLE
• PostgreSQL: cambiare il tipo di una colonna
• Come aggiungere colonne in Teradata

Queste operazioni sono fondamentali per mantenere e far evolvere lo schema del tuo database man mano che cambiano i requisiti della tua applicazione.

Conclusione

Usare ALTER TABLE per aggiungere colonne ti permette di modificare lo schema senza perdere dati. Questa flessibilità ti aiuta ad adattare il database ai requisiti in evoluzione della tua applicazione. Fai sempre un backup dei dati prima di modificare lo schema e verifica la compatibilità con la logica applicativa esistente.

Per operazioni più avanzate e per gestire i tuoi database SQL in modo efficace, prova Beekeeper Studio, che offre un'interfaccia intuitiva per gestire lo schema del database e le operazioni SQL.