Colonne Identity su Sql Server: tutto quello che c’è da sapere

Su un database Sql Server, la proprietà Identity permette di demandare al database la popolazione della colonna a cui tale proprietà è applicata. In questo articolo vedremo concretamente come creare con l’SQL una tabella con una colonna di tipo Identity e come essa si comporta in fase di aggiornamento del database.

Creare una colonna con la proprietà Identity

La tabella Customers generata con il seguente codice SQL contiene la colonna CustomerId con la proprietà Identity.

CREATE TABLE Customers (
   CustomerId INT NOT NULL 
                       IDENTITY(1,1) PRIMARY KEY, 
   CustomerName VARCHAR(50) NOT NULL);

La proprietà Identity ha due argomenti:

  • il primo valore indica il numero di partenza con il quale la colonna sarà popolata;
  • il secondo valore indica l’incremento che verrà effettuato ad ogni nuova riga.

La scelta di valorizzare entrambi gli argomenti con uno è molte frequente. Il valore massimo contenuto all’interno della colonna dipende dal suo tipo. Se ad esempio scegliamo l’intero INT, il valore massimo sarà 2147483647. Il successivo inserimento provocherà un errore di overflow aritmetico.

Inserire righe in una tabella con la proprietà Identity

Di default la colonna con la proprietà Identity non può essere specificata all’interno della lista delle colonne da popolare tramite una INSERT. Ad esempio la query seguente fallisce:

INSERT INTO Customers(
             CustomerId,
             CustomerName)
VALUES (1, 'a');

L’errore generato è abbastanza esplicativo:

Messaggio 544, livello 16, stato 1, riga 8
Quando IDENTITY_INSERT è OFF non è possibile inserire un valore esplicito per la colonna Identity

Occorre dunque riscrivere la query precedente eliminando i riferimenti alla colonna CustomerId:

INSERT INTO Customers(
             CustomerName)
VALUES ('a');

In questo modo la query compila e interrogando la tabella con una “select *” otterrò automaticamente il valore 1 nella colonna CustomerId.

Se per qualche motivo ho bisogno di forzare l’aggiornamento o la modifica della colonna CustomerId, sarà necessario lanciare il comando SET IDENTITY_INSERT <NomeTabella> ON, eseguire la INSERT o l’UPDATE e infine ripristinare l’opzione di partenza con SET IDENTITY_INSERT <NomeTabella> OFF.

Effetto di violazione di vincoli su una colonna Identity

Proviamo a forzare un errore in un operazione di INSERT nella tabella Customers. Possiamo ad esempio tentare di inserire il valore 1/0.

INSERT INTO Customers(
             CustomerName)
VALUES (1/0);

Ovviamente la query non andrà a buon fine. Ciò che potrebbe sorprendere è che, in un certo senso, il valore predisposto dall’Identity è stato comunque consumato. Lanciando una nuova insert senza nessun tipo di errore, ci accorgeremo che nella colonna Identity sarà presente un “salto”, il valore passerà ad esempio da 1 a 3. Attenzione dunque a non utilizzare i valori presenti nella colonna come un’indicazione precisa del numero di righe presenti nella tabella.

Per le differenze sugli effetti che le operazioni di delete e di truncate hanno su una colonna identity, vi rimando a quest’altro mio articolo.

Rimuovere la proprietà Identity da una colonna

Purtroppo su Sql Server non è possibile rimuovere la proprietà Identity da una colonna. Un approccio spesso consigliato sul web consiste nel creare una nuova colonna, aggiornarla con i valori presenti nella colonna Identity, eliminare la colonna Identity e rinominare la nuova colonna.

Molto spesso queste operazioni non sono sufficienti nei casi reali perché le colonne con la proprietà Identity sono quasi sempre scelte come chiavi primarie delle tabelle in cui sono presenti, oltre a essere presenti in indici, vincoli di chiave esterna, check, eccetera. Diventerebbe fondamentale dunque ricreare tutte queste dipendenze sulla nuova colonna, senza tralasciarne nessuna. Infine, va sottolineato che la nuova colonna sarebbe posizionata automaticamente in fondo alla lista. Questo potrebbe interferire con tutte le query dove l’ordine delle colonne non è specificato (basta pensare alle “SELECT *”).

In questa pagina https://docs.microsoft.com/it-it/sql/relational-databases/tables/change-column-order-in-a-table?view=sql-server-ver15 trovi la documentazione ufficiale su come modificare l’ordine delle colonne su Sql Server.

Alternative alla colonna Identity

La principale alternativa all’utilizzo di una colonna Identity è la creazione di una Sequence congiunta alla colonna desiderata tramite l’impostazione di un valore di Default.

Tutto quanto scritto in questa pagina fa riferimento al RDBMS Microsoft Sql Server. Troviamo concetti simili anche nelle versioni più recenti di tutti gli altri database, spesso con una sintassi leggermente diversa e comportamenti non sempre del tutto equivalenti. Su Oracle e PostgreSQL scriviamo GENERATED ALWAYS AS IDENTITY, mentre su MySql utilizziamo la parola chiave AUTO_INCREMENT.

Continua a imparare

Torna in alto
Torna su