Modifica delle tabelle piene di dati

Aggiungere un campo NOT NULL ad una tabella con dati preesistenti

Supponendo di avere questa tabella:

Tabella 8. Tabella avventure

Nome	Data_acquisto	Prezzo
Goffredo di Buglione	12-06-1995	€ 49,00
Giuseppe Garibaldi	19-10-1995	€ 54,95

In questa tabella di improbabili giochi ci sono già alcune registrazioni quando si decide di dover aggiungere un campo ID sempre diverso da NULL. Ci sono due diverse soluzioni, ma ciascuna si porta problemi specifici da valutare.

Aggiungere un campo NOT NULL

Questo è il metodo preferito, forse perchè più immediato, ma provoca tutta una serie di problemi se usato in una tabella con dati già presenti come vedremo. Innazitutto, si supponga di aggiungere un campo con il seguente comando:

alter table Avventure add id int not null

Dopo la conferma (commit), il nuovo campo ID in tutte le righe già esistenti, avrà valore NULL. In questo caso speciale, Firebird permette pertanto la presenza di dati non validi in una colonna NOT NULL. Non solo, è in grado di farne il backup senza problemi apparenti, ma si rifiuterà di recuperare i dati salvati, precisamente perchè essi violano il vincolo NOT NULL.

Nota

Firebird 1.5 (ma non 1.0 o 2.0) permettono perfino di rendere tale colonna una chiave primaria!

Valorizzazione errata di `NULL` come se fosse zero

A rendere le cose anche peggiori, Firebird mente quando si leggono i dati dalla tabella. Con isql e molti altri programmi, «SELECT * FROM AVVENTURE» riporta questo insieme di dati:

Tabella 9. Dati riportati dopo aver aggiunto una colonna NOT NULL

Nome	Data_acquisto	Prezzo	ID
Goffredo di Buglione	12-06-1995	€ 49,00	0
Giuseppe Garibaldi	19-10-1995	€ 54,95	0

Naturalmente questo farà pensare a molta gente «Ma che bello! Firebird usa un default valore di 0 per i nuovi campi: non c'è problema allora». Ma si riesce a verificare che il campo ID è veramente tutto a NULL con queste query:

SELECT * FROM AVVENTURE WHERE ID = 0 (non riporta nulla)
SELECT * FROM AVVENTURE WHERE ID IS NULL (riporta il set visto qui sopra con tutti gli 0 fasulli)
SELECT * FROM AVVENTURE WHERE ID IS NOT NULL (non riporta nulla)

Un altro tipo di query che dimostra che qualcosa di strano sta' succedendo è la seguente:

SELECT NOME, ID, ID+3 FROM AVVENTURE

Questa query riporta 0 nella colonna «ID+3». Se ci fosse stato un vero e proprio 0 in ID avrebbe dovuto esserci 3. Il risultato corretto dovrebbe essere NULL, naturalmente!

Con un campo di tipo (VAR)CHAR, si avrebbero delle stringhe vuote fasulle (''). Con colonne di tipo DATE, ci sarebbero delle «date zero» fasulle pure quelle, che indicano il giorno 17 Novembre 1858. In tutti i casi, il vero valore della data è NULL.

Spiegazione

Che cosa succede?

Quando una applicazione come isql interroga il server, la conversazione avviene attraverso un certo numero di passi. Durante una di queste, la fase di descrizione – «describe» – il sistema informa del tipo e della annullabilità per ogni colonna che appare nell'insieme del risultato. Fa questo in una struttura che viene succesivamente utilizzata per recuperare il valore attuale del dato. Per le colonne che sono marchiate con NOT NULL dal server, non c'è alcun modo per riportare il NULL al client — a meno che il client reimposti l'informazione prima di iniziare la fase di recupero dei dati. Molte applicazioni client non lo fanno. D'altra parte, se il server assicura che una colonna non può contenere NULL, per quale motivo si dovrebbe pensare di saperne di più, e soprassedere alla sua decisione controllando comunque i NULL? Ma purtroppo è esattamente quello che andrebbe fatto se si vuole evitare il rischio di riportare valori fasulli agli utenti.

FSQL

L'esperto di Firebird Ivan Prenosil ha scritto un programma libero a linea di comando che funziona più o meno come isql, che, tra gli altri perfezionamenti, riporta i NULL correttamente anche nelle colonne NOT NULL. Si chiama FSQL e lo si può scaricare da:

http://www.volny.cz/iprenosil/interbase/fsql.htm

Assicurarsi della validità dei dati

Per essere sicuri di avere dei dati validi quando si aggiunge una colonna NOT NULL ad una tabella con dati preesistenti bisogna fare una delle operazioni seguenti:

per impedire il problema delle colonne non null invece che null, si può aggiungere un valore di default aggiungendo la nuova colonna:
```
alter table Avventure add id int default -1 not null
```
I valori di default non sono applicati di solito quando si aggiungono campi a righe esistenti, ma lo sono con campi NOT NULL.
Altrimenti, si possono assegnare ai nuovi campi i valori che dovrebbero avere in modo esplicito, subito dopo aver aggiunto la relativa colonna. Si può verificare che sono tutti validi con una query «SELECT ... WHERE ... IS NULL», che dovrebbe riportare un insieme di righe vuoto.
Se il danno è stato fatto e ci si ritrova con una copia di backup impossibile da recuperare, si può usare la variante -n del programma gbak per ignorare i vincoli di validità nel restore. Dopodichè è meglio aggiustare i dati ed i vincoli manualmente. Di nuovo, si verifica il tutto con una query «WHERE ... IS NULL».

Importante

Le versioni di Firebird fino alla 1.5.0 inclusa hanno un problema che fa rimettere alla gbak i vincoli NOT NULL anche specificando il -n. Con tali versioni, se si è fatto un backup del database con dati NULL in campi NOT NULL, si è veramente nei guai. Soluzione: installare la versione 1.5.1 o successiva, recuperare i dati con gbak -n e poi aggiustarli.

Aggiungere un campo CHECK

Usare un vincolo CHECK è un modo diverso per impedire valori NULL in una colonna:

alter table Avventure add id int check (id is not null)

Se lo fai in questo modo, una successiva SELECT darà:

Tabella 10. Result set dopo aver aggiunto un campo CHECK

Nome	Data_acquisto	Prezzo	ID
Goffredo di Buglione	12-06-1995	€ 49,00	<null>
Giuseppe Garibaldi	19-10-1995	€ 54,95	<null>

Be', almeno adesso si riesce a vedere che i campi sono NULL! Firebird non attua i vincoli di CHECK sulle righe preesistenti aggiungendo nuovi campi. Questo è vero anche aggiungendo controlli a campi esistenti con ADD CONSTRAINT o con ADD CHECK.

In questo caso, Firebird non solo tollera la presenza ed il backup dei valori NULL, ma ne permette pure il recupero. Lo strumento gbak di Firebird recupera i vincoli di CHECK, ma non li applica ai dati esistenti nel backup.

Nota

La gbak rimette i vincoli di CHECK anche con lo switch -n. Ma siccome non vengono utilizzati ver convalidare i dati di backup, questo non comporterà mai errori in fase di recupero.

La recuperabilità dei dati NULL nonostante la presenza di vincoli CHECK è consistente col fatto che Firebird permette loro di essere presenti inizialmente e di farne il backup. Da un punto di vista pratico, c'è il rovescio della medaglia: si può fare tutto un ciclo di backup e restore con i dati «illegali» che sopravvivono senza che neanche se abbia la minima notizia o avvertimento. Pertanto: bisogna essere sempre sicuri che i dati esistenti siano consistenti con le regole aggiornate subito dopo aver aggiunto la nuova colonna con vincoli. Notare che il trucco del «default» in questo caso non funzionerebbe; bisogna ricordarsi di mettere i valori giusti subito. Se ce lo si scorda, ci sono serie possibilità che sopravvivano per lungo tempo dei NULL fuorilegge, in quanto non ci saranno poi più sveglie inserite.

Aggiungere un campo non annullabile usando i domini

Al posto di specificare il tipo di dato ed il vincolo direttamente, si possono usare anche i domini, ad esempio così:

create domain icnn as int check (value is not null);
alter table Avventure add id icnn;

Per quanto riguarda la presenza di campi NULL, il riportare valori fasulli a 0, l'effetto dei valori default, ecc., non fa alcuna differenza fra scegliere il metodo dei domini o l'approccio diretto. Tuttavia, un vincolo NOT NULL che viene posto attraverso un dominio può essere sempre rimosso, un vincolo NOT NULL diretto su una colonna ci starà per sempre.

Rendere le colonne esistenti non annullabili

Rendere una colonna esistente NOT NULL

Non si può aggiungere il vincolo NOT NULL ad una colonna esistente, ma si può aggirare il problema semplicemente; supponendo che il tipo sia intero, allora con

create domain intnn as int not null;
alter table Tabella alter Colonna type intnn;

cambierà il tipo della colonna in «int not null».

Se la tabella ha già delle registrazioni, ogni NULLs già presente nella colonna rimarrà NULL, e come sopra Firebird lo riporterà come 0 all'utente nelle interrogazioni. La situazione è abbastanza simile a (vedi) aggiungere un campo NOT NULL. La sola differenza è che dando ad un dominio (e pertanto alla colonna) un valore default, in questo caso non si può essere sicuri che esso verrà applicato ai preesistenti valori NULL. Le prove mostrano che talvolta il default viene applicato a tutti i NULLs, qialche volta a nessuno, ed in qualche raro caso ad alcuni dei valori esistenti ma non a tutti! Ultima cosa: cambiando il tipo di dato della colonna, qualora il nuovo tipo includa un default, bisogna controllare tutti i valori già esistenti, specialmente se «sembrano contenere» 0 o valori equivalenti a zero.

Aggiungere un vincolo di CHECK ad una colonna esistente

Ci sono due modi per aggiungere un vincolo di CHECK ad una colonna:

alter table Articoli add check (Valore is not null)

alter table Articoli add constraint ValoreNonNull check (Valore is not null)

La seconda forma è la preferita, in quanto permette in modo semplice di eliminare il controllo, per quanto funzionino esattamente allo stesso modo. Come ci si può facilmente aspettare, i campi con NULL già esistenti nella colonna rimangonon, può esserne fatto il backup ed il restore, ecc. ecc. Vedi aggiungere un campo CHECK.

Rendere le colonne non annullabili di nuovo annullabili

Se una colonna era stata resa non annullabile con un vincolo CHECK, quest'ultimo può essere eliminato:

alter table Articoli drop constraint ValoreNonNull

Avendo dato un nome al vincolo ma con il CHECK aggiunto direttamente alla colonna o alla tabella, bisogna trovare il suo nome prima di poterlo rimuovere. Questo si può fare con il comando isql «SHOW TABLE» (in questo caso: SHOW TABLE ARTICOLI).

In caso di un vincolo NOT NULL, conoscendo il suo nome si può semplicemente rimuoverlo:

alter table Articoli drop constraint NN_Valore

Se non si conosce il nome si può provare con isql ed usare al solito «SHOW TABLE», ma questa volta mostrerà il nome del vincolo solo se è stato ridefinito dall'utente. Il nome esatto si ha in ogni caso con:

select rc.rdb$constraint_name
from   rdb$relation_constraints rc
       join rdb$check_constraints cc
       on rc.rdb$constraint_name = cc.rdb$constraint_name
where  rc.rdb$constraint_type   = 'NOT NULL'
       and rc.rdb$relation_name = '<TableName>'
       and cc.rdb$trigger_name  = '<FieldName>'

Non c'è da spaccarsi la testa sui nomi dei campi e delle tabelle di questa query: non c'è dubbio che siano abbastanza strani, ma sono corretti. Basta solo assicurarsi che il nome della tabella e del campo siano in maiuscolo se sono stati definiti in modo indipendente dal caso. Altrimenti devono essere scritti in modo esatto.

Se il vincolo NOT NULL arriva attraverso un dominio, si può rimuovere modificando il tipo di colonna ad un dominio annullabile o ad un tipo di dato predefinito:

alter table Articoli alter Importo type int

Ogni NULL nascosto, se presente, sarà di nuovo visibile.

In qualsiasi modo si rimuova il vincolo NOT NULL, bisogna confermare il lavoro (commit) e chiudere tutte le connessioni al database. Dopodichè ci si può riconnettere ed inserire NULL nelle colonne.