Guida SQLite interattiva: Vincoli UNIQUE

Q: Come aggiungo un vincolo UNIQUE in SQLite?

Aggiungi UNIQUE alla definizione di una colonna (email TEXT UNIQUE) oppure scrivi una clausola a livello di tabella UNIQUE(col1, col2) per l'unicità su più colonne. SQLite lo applica creando dietro le quinte un indice univoco e rifiutando qualsiasi INSERT o UPDATE che produrrebbe un duplicato.

Q: Che differenza c'è tra UNIQUE e PRIMARY KEY in SQLite?

Una tabella può avere una sola PRIMARY KEY ma molti vincoli UNIQUE. PRIMARY KEY implica anche NOT NULL (nelle tabelle strict e per INTEGER PRIMARY KEY), mentre le colonne UNIQUE possono contenere più valori NULL. Usa la chiave primaria per l'identità della riga e UNIQUE per le altre colonne che non devono avere duplicati.

Q: Perché SQLite permette più NULL in una colonna UNIQUE?

Perché SQL tratta NULL come 'sconosciuto', e due valori sconosciuti non sono considerati uguali. Quindi una colonna UNIQUE accetta tutte le righe NULL che vuoi: solo i valori non NULL devono essere distinti. Se ti serve al massimo un NULL, aggiungi NOT NULL o usa un indice univoco parziale.

Q: Come risolvo l'errore 'UNIQUE constraint failed'?

L'errore significa che un INSERT o un UPDATE creerebbe un valore duplicato in una colonna UNIQUE (o PRIMARY KEY). Puoi cambiare il valore che stai inserendo, eliminare prima la riga esistente oppure usare INSERT ... ON CONFLICT (un upsert) per dire a SQLite cosa fare quando si verifica il conflitto.

UNIQUE significa "niente duplicati"

Un vincolo UNIQUE dice a SQLite che i valori di una colonna (o di un gruppo di colonne) non devono ripetersi tra le righe. È il modo per dire "due utenti non possono avere la stessa email" oppure "un codice prodotto compare al massimo una volta".

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

Il terzo insert fallisce con UNIQUE constraint failed: users.email. SQLite controlla il vincolo a ogni scrittura e rifiuta tutto ciò che creerebbe un duplicato. Le prime due righe vengono salvate; la terza non arriva mai.

Dietro le quinte UNIQUE è implementato come indice univoco, la stessa struttura dati che SQLite usa per le ricerche veloci, quindi il controllo costa poco e la colonna viene indicizzata automaticamente.

Sintassi a livello di colonna e a livello di tabella

Puoi scrivere UNIQUE in due modi. In linea accanto a una colonna, oppure come clausola separata alla fine della definizione della tabella:

-- A livello di colonna: semplice, una sola colonna.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- A livello di tabella: stesso effetto, posizione diversa.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

Per una sola colonna le due forme sono equivalenti: scegli quella che si legge meglio. La forma a livello di tabella diventa indispensabile appena ti serve l'unicità su più di una colonna.

UNIQUE composto: più colonne insieme

A volte una singola colonna non è unica da sola, ma una combinazione dovrebbe esserlo. Un utente può iscriversi a molti corsi e un corso può avere molti utenti, ma la stessa coppia (user_id, course_id) non dovrebbe comparire due volte:

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- ok, corso diverso
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- ok, utente diverso
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- fallisce: la coppia esiste già

SELECT * FROM enrollments;

Il vincolo riguarda la coppia, non una delle due colonne da sola. L'utente 1 può iscriversi a molti corsi, il corso 100 può avere molti utenti, ma una sola volta per combinazione.

È lo schema di base per le tabelle di collegamento nelle relazioni molti a molti.

UNIQUE vs PRIMARY KEY

Sembrano simili e sono collegati, ma non sono la stessa cosa:

Una tabella ha al massimo una PRIMARY KEY. Può avere molti vincoli UNIQUE.
PRIMARY KEY è l'identità della riga: ciò a cui puntano le chiavi esterne, ciò di cui rowid è un alias.
UNIQUE significa solo "questo valore (o questa combinazione) non si ripete".
In una tabella normale, una colonna UNIQUE può contenere valori NULL; una PRIMARY KEY no (con un'eccezione storica che lasciamo perdere).

Una forma comune:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- identità interna, non cambia mai
    email    TEXT NOT NULL UNIQUE,  -- identificativo visibile all'utente, deve essere unico
    username TEXT NOT NULL UNIQUE   -- un altro identificativo unico
);

id è ciò a cui fa riferimento il resto del database. email e username sono unici perché lo richiede l'applicazione, non perché rappresentano l'identità. Se un utente cambia email, l'id resta lo stesso: è proprio questo il senso di tenerli separati.

La stranezza dei NULL

Questa fa inciampare quasi tutti la prima volta. In SQLite una colonna UNIQUE accetta tutti i valori NULL che vuoi:

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- questo fallisce

SELECT * FROM invites;

Tre NULL, nessun problema. Due 'ada@example.com', ed è un conflitto.

Il motivo: SQL tratta NULL come "sconosciuto", e due valori sconosciuti non sono considerati uguali, quindi il controllo di unicità non può dire che sono duplicati. Se ti serve al massimo un NULL, la soluzione più pulita è NOT NULL UNIQUE. Se i NULL sono validi ma solo uno per ogni combinazione con un'altra colonna, usa un indice parziale (lo vediamo più avanti nel capitolo sugli indici).

Gestire i conflitti: ON CONFLICT

Per impostazione predefinita, una violazione di UNIQUE interrompe l'istruzione. Ma a volte vuoi un comportamento diverso: sostituire la riga esistente, ignorare quella nuova o aggiornare colonne specifiche. SQLite ti offre due modi per chiederlo.

Il primo è integrato nel vincolo con ON CONFLICT:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- sostituisce la prima
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

La seconda volta che viene inserito theme, la riga esistente viene eliminata e quella nuova prende il suo posto. Le altre opzioni sono IGNORE (salta in silenzio), ABORT (il predefinito), FAIL e ROLLBACK.

Il secondo modo è per singola istruzione, con la sintassi upsert, di solito più flessibile perché può aggiornare colonne specifiche:

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

Il primo insert crea la riga. I due successivi urtano contro il vincolo UNIQUE e passano al ramo DO UPDATE, incrementando count. È lo schema upsert INSERT ... ON CONFLICT: più avanti c'è una pagina dedicata.

Vincolo UNIQUE vs indice UNIQUE

CREATE UNIQUE INDEX fa lo stesso lavoro di un vincolo UNIQUE. Anzi, un vincolo UNIQUE crea dietro le quinte un indice univoco: sono quasi lo stesso meccanismo con due vesti diverse.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- fallisce

Quando preferire l'uno o l'altro:

Vincolo quando l'unicità fa parte della definizione della tabella. È documentata proprio accanto alle colonne.
Indice univoco quando vuoi un indice parziale (clausola WHERE), ti serve un nome specifico o vuoi aggiungerlo a una tabella esistente senza riscriverla. L'ALTER TABLE di SQLite non può aggiungere un vincolo, ma un indice si può sempre aggiungere.

Il comportamento in scrittura è identico. La scelta riguarda soprattutto dove vuoi che la regola viva nello schema.

Aggiungere UNIQUE a una tabella esistente

L'ALTER TABLE di SQLite è volutamente limitato: non esiste ALTER TABLE ... ADD CONSTRAINT. Le due opzioni pratiche:

-- Opzione 1: aggiungere un indice univoco. Funziona sulle tabelle esistenti, stessa garanzia.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- ora fallisce

L'opzione 2, quando vuoi davvero una clausola UNIQUE incorporata nella definizione della tabella, è la procedura di riscrittura della tabella: crei una nuova tabella con il vincolo, copi i dati, elimini la vecchia e rinomini. La vediamo nella prossima pagina.

Un avvertimento: se aggiungi l'unicità a una colonna che ha già dei duplicati, il CREATE UNIQUE INDEX fallirà. Prima ripulisci le righe duplicate, poi aggiungi l'indice.

Quando UNIQUE fallisce: leggere l'errore

Il messaggio di errore ti dice esattamente quale vincolo è saltato:

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

La prima forma è un vincolo su una sola colonna, users.email. La seconda è composta: sono elencate entrambe le colonne perché è la combinazione a esistere già. Quando lo vedi:

Individua quale riga ha già il valore in conflitto (SELECT ... WHERE email = '...').
Decidi se vuoi aggiornare quella riga, saltare l'insert o usare un valore diverso.
Se i duplicati sono previsti e vuoi unirli, passa a INSERT ... ON CONFLICT DO UPDATE.

L'errore è rumoroso perché nella maggior parte dei casi vuoi davvero saperlo: i duplicati silenziosi sarebbero peggio di una scrittura fallita.

Prossimo passo: eliminare e modificare le tabelle

I vincoli UNIQUE non si possono aggiungere a una tabella esistente con un semplice ALTER TABLE. Questo limite è il motivo per cui SQLite ha una procedura particolare per le modifiche allo schema, la riscrittura della tabella, ed è l'argomento della prossima pagina, insieme alle basi per eliminare le tabelle in modo pulito.

Domande frequenti

Come aggiungo un vincolo UNIQUE in SQLite?

Aggiungi UNIQUE alla definizione di una colonna (email TEXT UNIQUE) oppure scrivi una clausola a livello di tabella UNIQUE(col1, col2) per l'unicità su più colonne. SQLite lo applica creando dietro le quinte un indice univoco e rifiutando qualsiasi INSERT o UPDATE che produrrebbe un duplicato.

Che differenza c'è tra UNIQUE e PRIMARY KEY in SQLite?

Una tabella può avere una sola PRIMARY KEY ma molti vincoli UNIQUE. PRIMARY KEY implica anche NOT NULL (nelle tabelle strict e per INTEGER PRIMARY KEY), mentre le colonne UNIQUE possono contenere più valori NULL. Usa la chiave primaria per l'identità della riga e UNIQUE per le altre colonne che non devono avere duplicati.

Perché SQLite permette più NULL in una colonna UNIQUE?

Perché SQL tratta NULL come 'sconosciuto', e due valori sconosciuti non sono considerati uguali. Quindi una colonna UNIQUE accetta tutte le righe NULL che vuoi: solo i valori non NULL devono essere distinti. Se ti serve al massimo un NULL, aggiungi NOT NULL o usa un indice univoco parziale.

Come risolvo l'errore 'UNIQUE constraint failed'?

L'errore significa che un INSERT o un UPDATE creerebbe un valore duplicato in una colonna UNIQUE (o PRIMARY KEY). Puoi cambiare il valore che stai inserendo, eliminare prima la riga esistente oppure usare INSERT ... ON CONFLICT (un upsert) per dire a SQLite cosa fare quando si verifica il conflitto.

Concetti correlati