Guida SQLite interattiva: Chiavi primarie

Q: Come definisco una chiave primaria in SQLite?

Aggiungi PRIMARY KEY a una colonna nella tua istruzione CREATE TABLE, per esempio id INTEGER PRIMARY KEY. Per una chiave su più colonne, usa un vincolo a livello di tabella: PRIMARY KEY (cola, colb). La colonna o la combinazione deve essere unica tra tutte le righe.

Q: Che differenza c'è tra INTEGER PRIMARY KEY e le altre chiavi primarie in SQLite?

INTEGER PRIMARY KEY è speciale: diventa un alias del rowid integrato della tabella, quindi viene salvato direttamente nel B-tree senza un indice aggiuntivo. Qualsiasi altro tipo, o una chiave composta, riceve un indice unique separato. Per gli id numerici su una sola colonna, INTEGER PRIMARY KEY è più veloce e più compatto.

Q: Mi serve AUTOINCREMENT su una chiave primaria di SQLite?

Di solito no. Un INTEGER PRIMARY KEY assegna già in automatico un rowid unico quando inserisci NULL. AUTOINCREMENT aggiunge solo la garanzia che gli id non vengano mai riutilizzati dopo una cancellazione, al prezzo di una tabella sqlite_sequence in più. Lascialo perdere a meno che ti serva proprio quel comportamento con id sempre crescenti.

Cosa fa davvero una chiave primaria

Una chiave primaria è la colonna (o la combinazione di colonne) che identifica in modo univoco ogni riga di una tabella. Due righe non possono avere lo stesso valore di chiave primaria. SQLite lo garantisce al posto tuo e usa la chiave per trovare le righe in fretta.

La forma più semplice si scrive direttamente sulla colonna:

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

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

SELECT * FROM users;

Non hai fornito un id e SQLite ne ha inserito uno. Non è magia: è un caso speciale di INTEGER PRIMARY KEY che vale la pena capire prima di scrivere qualsiasi altra cosa.

INTEGER PRIMARY KEY è speciale

Nella maggior parte dei database, una chiave primaria è solo un indice unique. In SQLite, ogni tabella normale ha già un intero nascosto a 64 bit chiamato rowid che identifica le righe internamente. Quando dichiari una colonna esattamente come INTEGER PRIMARY KEY, quella colonna diventa il rowid. Nessun indice in più, nessuno spazio in più: il tuo id e la posizione fisica della riga sono la stessa cosa.

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

INSERT INTO posts (title) VALUES ('Primo'), ('Secondo'), ('Terzo');

SELECT id, rowid, title FROM posts;

id e rowid sono la stessa colonna con due nomi. Le ricerche per id vanno dritte alla riga: non c'è un secondo albero da percorrere. Ecco perché il consiglio standard per SQLite è: se vuoi una chiave primaria numerica, scrivi esattamente INTEGER PRIMARY KEY. Non INT, non BIGINT, non INTEGER NOT NULL PRIMARY KEY (in realtà quest'ultima funziona, ma il tipo deve essere INTEGER).

Gli altri tipi funzionano comunque: ricevono solo un indice unique separato, il che va bene, ma è meno compatto.

AUTOINCREMENT di solito non serve

Un riflesso comune per chi arriva da altri database è scrivere id INTEGER PRIMARY KEY AUTOINCREMENT. In SQLite la parola chiave AUTOINCREMENT fa qualcosa di più limitato di quanto suggerisca il nome, e quasi sempre non ti serve.

Senza AUTOINCREMENT, una colonna INTEGER PRIMARY KEY si riempie in automatico con il valore successivo al rowid più alto esistente. Se cancelli l'ultima riga, l'insert successivo potrebbe riutilizzare quell'id.

Con AUTOINCREMENT, SQLite tiene traccia dell'id più alto mai usato in una tabella a parte chiamata sqlite_sequence e non riutilizza mai i valori, nemmeno dopo una cancellazione.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('nuovo');
INSERT INTO strict_id (label) VALUES ('nuovo');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

La tabella plain ha riutilizzato l'id 3. La tabella con AUTOINCREMENT è passata a 4. A meno che tu non abbia un motivo reale per vietare il riuso degli id, come audit o riferimenti esterni che restano dopo la cancellazione, lascia perdere AUTOINCREMENT. Costa una scrittura in più a ogni insert e una tabella di servizio separata.

Chiavi primarie composte

A volte una sola colonna non basta. Una tabella di collegamento che associa utenti e ruoli, per esempio, è identificata in modo univoco dalla coppia (user_id, role_id). In questo caso dichiara la chiave a livello di tabella:

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- Questa viola la chiave composta:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

La coppia deve essere unica in tutta la tabella: (1, 10) può comparire una volta sola. Ciascuna colonna da sola può ripetersi liberamente. Il punto è proprio questo: ogni utente può avere molti ruoli, ogni ruolo può avere molti utenti, ma una data coppia utente-ruolo esiste al massimo una volta.

Una chiave primaria composta crea un indice separato che copre le colonne elencate. Non diventa il rowid: solo un singolo INTEGER PRIMARY KEY riceve quel trattamento.

Il tranello dei NULL nella chiave primaria

Ecco una stranezza che sorprende chi arriva da PostgreSQL o MySQL: in una normale tabella SQLite, una colonna di chiave primaria diversa da INTEGER PRIMARY KEY può contenere NULL. È un bug di lunga data che gli autori di SQLite hanno mantenuto per compatibilità con il passato.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Mela');
INSERT INTO products (sku, name) VALUES (NULL, 'Mistero 1');
INSERT INTO products (sku, name) VALUES (NULL, 'Mistero 2');

SELECT * FROM products;

Due righe con NULL hanno superato la chiave primaria. La soluzione è aggiungere esplicitamente NOT NULL su ogni colonna di chiave primaria non intera:

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Mela');
INSERT INTO products (sku, name) VALUES (NULL, 'Mistero');  -- ora viene rifiutata

Oppure usa una tabella STRICT, in cui il bug dei NULL nella chiave primaria è corretto. L'abitudine di scrivere NOT NULL su ogni colonna di chiave primaria è un'assicurazione che costa poco.

Chiave primaria e UNIQUE

Entrambe impediscono i duplicati. Le differenze:

Una tabella ha al massimo una chiave primaria, ma può avere molti vincoli UNIQUE.
La chiave primaria è l'identificatore "principale" della tabella: le chiavi esterne puntano a lei per impostazione predefinita.
Un INTEGER PRIMARY KEY diventa il rowid; una colonna intera UNIQUE no.
Le colonne UNIQUE accettano tranquillamente più NULL (ogni NULL è considerato distinto).

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE
);

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id è l'identità della riga. Anche email e username sono uniche, ma sono attributi di business: potrebbero cambiare, mentre l'id non dovrebbe.

Aggiungere una chiave primaria in seguito (in genere: non farlo)

L'ALTER TABLE di SQLite è limitato. Non puoi eseguire ALTER TABLE ... ADD PRIMARY KEY: quell'istruzione non esiste. Se hai dimenticato la chiave primaria e la tabella ha già dei dati, la strada è ricrearla:

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- Ricostruisci con una chiave primaria:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

È la classica danza di migrazione di SQLite. Nel codice reale racchiudila in una transazione e disattiva per un momento le chiavi esterne se altre tabelle fanno riferimento a questa. La lezione: imposta bene la chiave primaria già al momento del CREATE TABLE.

Una checklist veloce

Quando scrivi una nuova tabella, chiediti:

Questa riga ha un id univoco naturale? Se è un singolo intero, usa INTEGER PRIMARY KEY.
L'identità è in realtà una combinazione di colonne (una tabella di collegamento)? Usa un PRIMARY KEY (col_a, col_b) a livello di tabella.
La chiave è testo o un altro tipo non intero? Aggiungi esplicitamente NOT NULL.
Ti serve davvero AUTOINCREMENT? Probabilmente no.
La tabella è piccola, letta più che scritta, con una chiave primaria non intera? Valuta WITHOUT ROWID (trattato nella pagina sul rowid).

Prossimo passo: rowid

INTEGER PRIMARY KEY è comparso di sfuggita come "un alias del rowid", ma il rowid è la base di ogni tabella SQLite normale e vale la pena capirlo direttamente. È la prossima pagina.

Domande frequenti

Come definisco una chiave primaria in SQLite?

Aggiungi PRIMARY KEY a una colonna nella tua istruzione CREATE TABLE, per esempio id INTEGER PRIMARY KEY. Per una chiave su più colonne, usa un vincolo a livello di tabella: PRIMARY KEY (col_a, col_b). La colonna o la combinazione deve essere unica tra tutte le righe.

Che differenza c'è tra INTEGER PRIMARY KEY e le altre chiavi primarie in SQLite?

INTEGER PRIMARY KEY è speciale: diventa un alias del rowid integrato della tabella, quindi viene salvato direttamente nel B-tree senza un indice aggiuntivo. Qualsiasi altro tipo, o una chiave composta, riceve un indice unique separato. Per gli id numerici su una sola colonna, INTEGER PRIMARY KEY è più veloce e più compatto.

Mi serve AUTOINCREMENT su una chiave primaria di SQLite?

Di solito no. Un INTEGER PRIMARY KEY assegna già in automatico un rowid unico quando inserisci NULL. AUTOINCREMENT aggiunge solo la garanzia che gli id non vengano mai riutilizzati dopo una cancellazione, al prezzo di una tabella sqlite_sequence in più. Lascialo perdere a meno che ti serva proprio quel comportamento con id sempre crescenti.

Perché la mia chiave primaria in SQLite accetta valori NULL?

È un bug storico, mantenuto per compatibilità: nelle tabelle normali, una colonna di chiave primaria non INTEGER può accettare NULL a meno che tu non aggiunga esplicitamente NOT NULL. La colonna INTEGER PRIMARY KEY è l'eccezione: non accetta mai NULL. Per sicurezza, scrivi NOT NULL su ogni colonna di chiave primaria, oppure usa una tabella STRICT, dove la regola viene applicata correttamente.

Concetti correlati