Come usare ALTER TABLE per aggiungere una colonna a una tabella di SQL Server e MySQL

In questo articolo vedremo analogie e differenze tra SQL Server e MySQL nell’utilizzo dell’istruzione ALTER TABLE per aggiungere una colonna a una tabella già esistente.

Alter Table Add Column su SQL Server

Su SQL Server possiamo aggiungere una colonna con questo codice

ALTER TABLE <nome tabella> 
ADD <nome colonna> <tipo> <vincolo>;

Ad esempio, per aggiungere la colonna Cognome alla tabella Clienti, specificando che potranno essere presenti dei NULL, scriverò

ALTER TABLE dbo.Clienti
ADD Cognome NULL;

Su SQL Server non è possibile aggiungere una colonna con il vincolo NOT NULL a una tabella che contiene già dei dati. I passi da seguire saranno quindi:

  1. aggiungere inizialmente la colonna con il vincolo NULL;
  2. aggiornare tutte le righe con un Update;
  3. modificare la colonna imponendo il vincolo NOT NULL.

La situazione cambia se assegniamo alla colonna un Default, come mostrato nel paragrafo successivo.

Alter Table su SQL Server con DEFAULT

Consideriamo il codice seguente su SQL Server dove utilizziamo un Default in congiunzione con il vincolo NOT NULL:

ALTER TABLE Clienti
ADD Cognome NOT NULL DEFAULT('Iantomasi');

L’effetto sarà di creare una nuova colonna Cognome valorizzata con Iantomasi per tutte le righe preesistenti. Tale valore sarà inserito di default anche per le nuove righe per cui non specifichiamo tale colonna in fase di INSERT.

La situazione cambia se utilizziamo un default con il vincolo NULL. Il codice seguente

ALTER TABLE Clienti
ADD Cognome NULL DEFAULT('Iantomasi');

crea una colonna con tutti NULL, il default varrà solo per i successivi inserimenti. Se volessi aggiungere direttamente il valore contestualmente alla creazione della colonna, dovrei aggiungere l’opzione WITH VALUES. Il codice diventerebbe dunque

ALTER TABLE Clienti
ADD Cognome NULL DEFAULT('Iantomasi') WITH VALUES;
Video Corso Data Analysis completo

Alter Table Add Column su MySQL e differenze con SQL Server

Su MySQL posso usare la stessa sintassi di base di SQL Server per aggiungere una colonna:

ALTER TABLE <nome tabella> ADD <nome colonna> <vincolo>

Tuttavia esistono delle importanti differenze rispetto a SQL Server. In primo luogo, se aggiungiamo una colonna con il vincolo NOT NULL in una tabella che contiene già dei dati, MySQL creerà comunque la colonna valorizzandola in automatico con un valore dipendente dal tipo. Ad esempio:

  • aggiungendo una colonna di tipo INT inserirà degli zeri;
  • aggiungendo una colonna di tipo VARCHAR(50) inserirà la stringa vuota;
  • ma aggiungendo una colonna di tipo DATE, otterrò un errore e la colonna non sarà inserita.

Comunque, utilizzando il vincolo NULL, la nuova colonna sarà creata effettivamente con dei NULL.

Un’altra differenza con SQL Server è la seguente: su SQL Server aggiungere un Default a una colonna con il vincolo NULL non valorizza automaticamente la colonna (almeno di non inserire l’opzione WITH VALUES). Questo non vale per MySQL, dove il default avrà subito effetto.

Un’altra fondamentale differenza consiste nell’effetto dell’operazione ALTER TABLE su una transazione aperta. Su MySQL, a prescindere dall’esito dell’ALTER TABLE, con la sua esecuzione avverrà implicitamente anche il commit dell’eventuale transazione aperta prima dell’ALTER TABLE. Ciò invece non avviene su SQL Server. Analizziamo il codice seguente per un esempio concreto:

CREATE TABLE Test(col1 INT);
BEGIN TRANSACTION; 
   INSERT INTO Test(col1) 
   VALUES (1);
   
   ALTER TABLE Test 
   ADD col2 INT NULL;
ROLLBACK;
SELECT * 
FROM   test;

ed eseguiamolo su entrambi i DBMS (a meno di sostituire su MySQL BEGIN TRANSACTION con START TRANSACTION).

Otterremo due risultati differenti:

  • su SQL Server il ROLLBACK ha effetto sia sull’inserimento del valore 1 e sia sull’alter table. Di conseguenza la tabella resterà vuota;
  • su MySQL l’ALTER TABLE provocherà prima il commit implicito dell’inserimento precedente e poi il suo auto-commit. Di conseguenza il Rollback sarà irrilevante e la tabella si presenterà alla fine con due colonne e una riga.
col1col2
1null

Oltre all’ALTER TABLE, su MySQL questo comportamento è comune anche per altre istruzioni, puoi trovare un approfondimento qui https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

Continua a imparare

Torna in alto
Torna su