NOT NULL e DEFAULT in SQLite: vincoli di colonna che tengono

Due vincoli che si ripagano subito

La maggior parte dei bug che nascono da uno schema trascurato risale a una di due cose: una colonna che è NULL quando nessuno se lo aspettava, oppure una colonna a cui manca un valore che l'applicazione dava per scontato. NOT NULL e DEFAULT risolvono entrambe, e aggiungerli non costa quasi nulla.

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonimo',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

Una colonna è obbligatoria e non ha un valore di riserva. Due ce l'hanno. L'inserimento ha dovuto fornire solo email, e SQLite ha riempito il resto. È tutta la funzionalità in un esempio: il resto della pagina riguarda i casi limite.

NOT NULL significa "rifiuta NULL, senza eccezioni"

NOT NULL fa esattamente quello che dice. Qualsiasi tentativo di mettere NULL nella colonna, sia omettendola da un INSERT senza valore predefinito sia scrivendo NULL in modo esplicito, fallisce:

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Questo fallisce: manca title.
INSERT INTO posts (id) VALUES (1);

L'errore dice:

Runtime error: NOT NULL constraint failed: posts.title

Stesso risultato se passi NULL direttamente:

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Errore di runtime: NOT NULL constraint failed: posts.title

Questo è il contratto. Se una colonna è logicamente obbligatoria, contrassegnala come NOT NULL e avrai eliminato un'intera classe di bug: nessun codice applicativo potrà far passare di nascosto un NULL al database.

DEFAULT fornisce un valore quando chi inserisce non lo fa

DEFAULT entra in gioco solo quando un INSERT non cita affatto la colonna. Non salva un NULL esplicito:

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Scrivere la documentazione');
INSERT INTO tasks (title, status) VALUES ('Pubblicare', 'done');

SELECT * FROM tasks;

Il primo inserimento si affida al valore predefinito. Il secondo lo sovrascrive. Se avessi scritto INSERT INTO tasks (title, status) VALUES ('x', NULL), avresti ottenuto un errore NOT NULL constraint failed: la colonna è stata nominata, quindi il valore predefinito non scatta mai.

È il modello mentale da tenere a mente: DEFAULT sostituisce le colonne mancanti. NOT NULL rifiuta i null in qualunque modo arrivino. Sono funzionalità indipendenti che si combinano bene.

I valori predefiniti possono essere espressioni

Un valore predefinito letterale è il caso comune (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), ma SQLite accetta anche un'espressione tra parentesi. È così che timbri le righe con l'ora in cui sono state create, o generi un ID casuale:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

Alcune cose da sapere:

• L'espressione viene valutata a ogni inserimento, non una sola volta alla creazione della tabella. Ogni riga ottiene il proprio timestamp e il proprio token.
• CURRENT_TIMESTAMP, CURRENT_DATE e CURRENT_TIME sono le tre parole chiave speciali che non hanno bisogno di parentesi. Tutto il resto sì.
• L'espressione non può fare riferimento ad altre colonne o a subquery: deve essere autosufficiente.

Se vuoi una colonna facoltativa ma timbrata automaticamente, togli NOT NULL e tieni il valore predefinito. Se la vuoi obbligatoria e timbrata automaticamente, usa entrambi.

DEFAULT NULL è lecito (e a volte è proprio il punto)

Scrivere DEFAULT NULL equivale a non avere alcun valore predefinito: la colonna è NULL quando non fornisci un valore. Vale la pena usarlo quando vuoi rendere esplicito nello schema che "nessun valore" è lo stato iniziale previsto:

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

Qui bio e avatar si comportano in modo identico. Il DEFAULT NULL su bio è un commento sotto forma di codice: dice a chi legge lo schema che l'assenza di una bio è uno stato normale, non una svista.

Aggiungere NOT NULL a una tabella esistente

Qui le cose si complicano. ALTER TABLE in SQLite è volutamente limitato: non puoi eseguire ALTER COLUMN ... SET NOT NULL come faresti in Postgres. Quello che puoi fare dipende dal fatto che la colonna esista già o no.

Per una colonna nuova, ADD COLUMN ... NOT NULL funziona, ma devi fornire un valore predefinito: altrimenti le righe esistenti conterrebbero all'improvviso NULL in una colonna NOT NULL, il che è impossibile:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('widget'), ('gizmo');

-- Funziona: le righe esistenti ricevono il valore predefinito.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

Prova la stessa cosa senza valore predefinito e otterrai un errore:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Errore di runtime: Cannot add a NOT NULL column with default value NULL

Per una colonna esistente non c'è una modifica sul posto. La ricetta standard è la ricostruzione: crei una nuova tabella con il vincolo che vuoi, copi i dati, elimini la vecchia e rinomini la nuova. La vedremo nella pagina drop-and-alter-table: per ora ti basta sapere che il limite è reale e progettare lo schema tenendone conto.

Una combinazione realistica

La maggior parte delle tabelle in produzione usa entrambi i vincoli insieme per codificare "ciò che l'applicazione si aspetta che sia vero":

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

Leggi quello schema dall'alto in basso e puoi intuire cosa fa l'applicazione senza vedere una sola riga di codice. customer è obbligatorio e non ha un valore di riserva: chi inserisce deve sapere per chi è l'ordine. Importo, valuta e stato hanno tutti valori predefiniti sensati, quindi anche l'inserimento più semplice produce una riga coerente. notes è facoltativo. created_at viene riempito dal database, che è l'unico posto in cui dovrebbe essere riempito.

Ecco il valore di questi vincoli: trasformano le supposizioni in regole che il database stesso fa rispettare.

Errori comuni

Un breve elenco di cose che mettono in difficoltà:

• Un NULL esplicito vanifica DEFAULT. INSERT INTO t (col) VALUES (NULL) non usa il valore predefinito. La colonna deve essere assente dall'elenco delle colonne.
• Le espressioni predefinite vogliono le parentesi. DEFAULT CURRENT_TIMESTAMP funziona (è una delle tre parole chiave speciali). DEFAULT lower(hex(randomblob(8))) no: racchiudila, DEFAULT (lower(hex(randomblob(8)))).
• NOT NULL e la stringa vuota sono cose diverse. '' è un valore TEXT valido e non fa scattare il vincolo. Se vuoi vietare anche le stringhe vuote, serve un CHECK (prossima pagina).
• ADD COLUMN ... NOT NULL richiede un DEFAULT diverso da NULL. Senza, SQLite rifiuta la modifica.

Prossimo passo: i vincoli CHECK

NOT NULL e DEFAULT coprono "deve esistere" e "riempi se manca". Per il livello successivo di validazione, come "deve essere positivo", "deve essere uno di questi valori" o "la data di fine deve essere dopo quella di inizio", SQLite ha i vincoli CHECK, che ti permettono di scrivere espressioni booleane arbitrarie che ogni riga deve soddisfare. È il tema della prossima pagina.