CREATE TABLE in SQLite: sintassi, vincoli ed esempi

CREATE TABLE definisce uno schema

Ogni dato strutturato in SQLite vive in una tabella, e ogni tabella nasce da un'istruzione CREATE TABLE. Le dai un nome, elenchi le colonne e, se vuoi, aggiungi dei vincoli. SQLite scrive lo schema nel file del database e la tabella è pronta all'uso.

L'esempio utile più piccolo:

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

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

SELECT * FROM users;

Tre colonne, una chiave primaria, una regola NOT NULL. SQLite ha riempito id in automatico perché è una chiave primaria intera, e ha lasciato email a NULL nella seconda riga perché nessuna regola diceva il contrario. Questa è tutta la struttura, nome, colonne, vincoli, e tutto il resto della pagina ne è una variazione.

La sintassi, pezzo per pezzo

Una definizione di colonna è name TYPE constraint constraint .... Nel SQLite classico il tipo è facoltativo (ne parliamo nella pagina sull'affinità di tipo), ma è buona pratica indicarlo sempre: chi legge e gli strumenti ci fanno affidamento.

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    sku         TEXT    NOT NULL UNIQUE,
    name        TEXT    NOT NULL,
    price_cents INTEGER NOT NULL,
    in_stock    INTEGER NOT NULL DEFAULT 1
);

INSERT INTO products (sku, name, price_cents)
VALUES ('SKU-001', 'Notebook', 499);

SELECT * FROM products;

Alcune cose da notare:

• I vincoli si concatenano con degli spazi: NOT NULL UNIQUE su sku significa che valgono entrambe le regole.
• DEFAULT 1 su in_stock permette all'INSERT di saltare quella colonna.
• SQLite usa INTEGER per i booleani: non esiste un tipo BOOLEAN nativo. 0 è falso, 1 è vero.
• Una virgola dopo l'ultima colonna è un errore di sintassi. Qui SQL è più rigido di JavaScript.

IF NOT EXISTS: niente crash quando riesegui

Esegui un CREATE TABLE su un database che ha già quella tabella, e SQLite solleva un errore:

Error: table users already exists

La prima volta va bene, la centesima dà fastidio. IF NOT EXISTS fa sì che l'istruzione non faccia nulla quando la tabella esiste già:

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

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

SELECT name FROM sqlite_master WHERE type = 'table';

Il secondo CREATE TABLE non fa nulla: nessun errore, nessuna modifica allo schema. È la forma che vuoi nel codice di avvio, negli script di migrazione e ovunque lo stesso SQL possa essere eseguito più di una volta.

Un avvertimento, però: IF NOT EXISTS controlla solo il nome. Se esiste una tabella con quel nome ma con colonne diverse, SQLite la lascia com'è. Non "correggerà" né "aggiornerà" lo schema per te. A quello servono le migrazioni.

Vincoli: regole che viaggiano con lo schema

I vincoli sono il modo per spostare la validazione dentro il database stesso. Ecco quelli che userai di continuo:

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    UNIQUE (user_id, created_at)
);

INSERT INTO orders (user_id, total_cents) VALUES (1, 1500);
INSERT INTO orders (user_id, total_cents, status) VALUES (1, 800, 'shipped');

SELECT * FROM orders;

• PRIMARY KEY: identifica una riga in modo univoco. Ne parliamo meglio nella guida sulle chiavi primarie.
• NOT NULL: la colonna deve avere un valore.
• DEFAULT value: viene usato quando un INSERT omette la colonna. Può essere un letterale o un'espressione come datetime('now').
• CHECK (expr): deve risultare vero per ogni riga.
• UNIQUE (col, col): vincolo a livello di tabella che impone l'unicità sulla combinazione.

I vincoli vengono verificati a ogni INSERT e UPDATE. Una riga che ne viola uno viene rifiutata e l'istruzione fallisce. Intercettare i dati sbagliati nel database costa molto meno che intercettarli dopo che si sono diffusi nell'applicazione.

Chiavi esterne

Una chiave esterna dice "questa colonna punta a una riga di un'altra tabella". Mantiene i dati coerenti: non puoi fare riferimento a un utente che non esiste e (con le opzioni giuste) eliminare un utente può eliminare a cascata i suoi ordini.

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title   TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Ada');
INSERT INTO posts (user_id, title) VALUES (1, 'Hello, world');

SELECT p.id, u.name, p.title FROM posts p JOIN users u ON u.id = p.user_id;

Un'insidia di SQLite da ricordare: l'applicazione delle chiavi esterne è disattivata di default. Devi eseguire PRAGMA foreign_keys = ON per ogni connessione che vuole i vincoli controllati. La maggior parte dei driver applicativi lo fa per te o espone un'impostazione; se il tuo non lo fa, esegui il pragma subito dopo la connessione.

ON DELETE CASCADE qui significa che eliminare un utente elimina automaticamente i suoi post. Altre opzioni sono SET NULL, RESTRICT e quella predefinita, NO ACTION, che rifiuta l'eliminazione se esistono righe figlie.

CREATE TABLE AS SELECT

A volte vuoi una copia veloce del risultato di una query come nuova tabella: per uno snapshot, un backup o una tabella di lavoro durante un'analisi. CREATE TABLE ... AS SELECT fa proprio questo:

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL
);

INSERT INTO products (name, price_cents) VALUES
    ('Notebook', 499), ('Pen', 199), ('Stapler', 1299);

CREATE TABLE expensive_products AS
SELECT id, name, price_cents
FROM products
WHERE price_cents >= 500;

SELECT * FROM expensive_products;

La nuova tabella copia i nomi delle colonne, i tipi (per quanto possibile) e i dati. Quello che non copia è altrettanto importante: nessuna chiave primaria, nessun NOT NULL, nessun indice, nessuna chiave esterna. È uno snapshot piatto. Consideralo un punto di partenza per lavori estemporanei, non un modo per clonare uno schema vero.

Se vuoi la struttura senza i dati, aggiungi WHERE 0:

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL
);

CREATE TABLE products_archive AS SELECT * FROM products WHERE 0;

SELECT * FROM products_archive;
SELECT name FROM sqlite_master WHERE type = 'table';

Ottieni una tabella vuota con la stessa forma delle colonne: comoda per le tabelle di archivio che riempirai in seguito.

Tabelle temporanee

Una tabella TEMP vive solo per la connessione corrente al database. Chiudi la connessione e sparisce: niente pulizia, nessuno schema rimasto in giro:

CREATE TEMP TABLE recent_signups (
    user_id    INTEGER PRIMARY KEY,
    signed_up  TEXT NOT NULL
);

INSERT INTO recent_signups VALUES (1, '2026-01-15'), (2, '2026-01-18');

SELECT * FROM recent_signups;

Usi adatti: preparare righe per una query in più passaggi, tenere risultati intermedi troppo disordinati per una CTE, isolare dati di una singola connessione in una sessione lunga. CREATE TEMP TABLE e CREATE TEMPORARY TABLE significano la stessa cosa.

Puoi anche combinarla con AS SELECT: CREATE TEMP TABLE snapshot AS SELECT ... è uno schema comune per congelare un risultato a metà di un'analisi.

Nomi tra virgolette

Quasi sempre i nomi di colonne e tabelle sono identificatori semplici. Se devi usare una parola riservata o un nome con spazi, racchiudilo tra virgolette doppie (lo standard SQL) o tra backtick (un'abitudine di MySQL che SQLite accetta):

CREATE TABLE "order" (
    id        INTEGER PRIMARY KEY,
    "select"  TEXT,
    "user id" INTEGER
);

INSERT INTO "order" ("select", "user id") VALUES ('a', 1);
SELECT * FROM "order";

Funziona, ma è un attrito ogni volta che fai riferimento alla tabella. Preferisci nomi semplici come orders, selection, user_id ed evita del tutto le virgolette.

Un esempio realistico

Mettiamo insieme i pezzi: un piccolo schema per un'app di attività, con IF NOT EXISTS così può essere eseguito a ogni avvio:

PRAGMA foreign_keys = ON;

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

CREATE TABLE IF NOT EXISTS tasks (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       TEXT    NOT NULL,
    done        INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1)),
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO tasks (user_id, title) VALUES (1, 'Write the docs');

SELECT u.name, t.title, t.done FROM tasks t JOIN users u ON u.id = t.user_id;

Questo è uno schema che puoi mettere in produzione: creazione idempotente, chiavi esterne applicate, un CHECK che tiene done onesto, valori di default sensati e timestamp che si riempiono da soli.

Prossimo passo: i tipi di dati

CREATE TABLE ti permette di scrivere INTEGER, TEXT, REAL, ma SQLite è notoriamente tollerante su come salva quei valori. La prossima pagina spiega le cinque classi di archiviazione che SQLite usa davvero, e perché il tipo che hai scritto non è sempre il tipo che ottieni.