Docs SQLite exécutables : NOT NULL & DEFAULT

Q: Comment rendre une colonne obligatoire en SQLite ?

Il suffit d'ajouter NOT NULL à la définition de la colonne : email TEXT NOT NULL. Tout INSERT ou UPDATE qui laisse cette colonne à NULL échoue avec l'erreur NOT NULL constraint failed. Combine-le avec un DEFAULT si vous voulez une valeur de repli quand l'appelant n'en fournit pas.

Q: Comment fonctionnent les valeurs par défaut en SQLite ?

DEFAULT indique à SQLite quelle valeur utiliser quand un INSERT ne précise rien pour cette colonne. Le défaut peut être un littéral (DEFAULT 0, DEFAULT 'pending'), NULL, ou une expression entre parenthèses du genre DEFAULT (CURRENT_TIMESTAMP) ou DEFAULT (lower(hex(randomblob(8)))). Les défauts à base d'expression sont réévalués à chaque insertion.

Q: Peut-on ajouter NOT NULL à une colonne existante en SQLite ?

Pas directement : ALTER TABLE ... ALTER COLUMN n'existe pas en SQLite. Soit vous ajoutez une nouvelle colonne avec NOT NULL DEFAULT (le défaut est obligatoire pour les lignes existantes), soit vous reconstruisez la table : vous en créez une nouvelle avec la contrainte, vous copiez les données, vous supprimez l'ancienne et vous renommez la nouvelle.

Deux contraintes qui valent vraiment leur place

La plupart des bugs qui découlent d'un schéma bâclé se résument à deux choses : une colonne qui contient NULL alors que personne ne s'y attendait, ou une colonne sans valeur là où l'application en supposait une. La contrainte NOT NULL en SQLite et la valeur par défaut DEFAULT règlent les deux cas — et elles ne coûtent quasiment rien à mettre en place.

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

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

SELECT * FROM users;

Une colonne est obligatoire et n'a pas de valeur de repli. Les deux autres en ont une. L'INSERT n'a eu qu'à fournir email, et SQLite s'est chargé du reste. Voilà toute la fonctionnalité résumée en un exemple — le reste de cette page traite des cas particuliers.

La contrainte NOT NULL : aucun NULL toléré

La contrainte NOT NULL de SQLite fait exactement ce que son nom indique. Toute tentative d'insérer NULL dans la colonne échoue — que ce soit en omettant la colonne lors d'un INSERT sans valeur par défaut, ou en écrivant NULL explicitement :

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

-- Ceci échoue : title est manquant.
INSERT INTO posts (id) VALUES (1);

Le message d'erreur ressemble à ceci :

Runtime error: NOT NULL constraint failed: posts.title

Même résultat si vous passez NULL directement :

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Erreur d'exécution : NOT NULL constraint failed: posts.title

C'est ça le contrat. Si une colonne est logiquement obligatoire, marquez-la NOT NULL et vous éliminez d'un coup toute une catégorie de bugs — aucun code applicatif ne pourra plus glisser un NULL dans la base.

DEFAULT : une valeur par défaut quand l'appelant n'en fournit pas

La clause DEFAULT n'entre en jeu que si l'INSERT ne mentionne pas du tout la colonne. Elle ne rattrape pas un NULL explicite :

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

INSERT INTO tasks (title) VALUES ('Rédiger la documentation');
INSERT INTO tasks (title, status) VALUES ('Mettre en production', 'done');

SELECT * FROM tasks;

Le premier INSERT s'appuie sur la valeur par défaut. Le second l'écrase. Si vous aviez écrit INSERT INTO tasks (title, status) VALUES ('x', NULL), vous récupéreriez une erreur NOT NULL constraint failed : la colonne étant nommée explicitement, la valeur par défaut ne se déclenche jamais.

Voici le modèle mental à retenir : DEFAULT comble les colonnes absentes de la requête. NOT NULL rejette les NULL quelle que soit leur provenance. Ce sont deux mécanismes indépendants qui se combinent très bien.

Les valeurs par défaut peuvent être des expressions

Le cas le plus courant, c'est une valeur littérale (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), mais SQLite accepte aussi une expression entre parenthèses. C'est comme ça qu'on horodate une ligne au moment de sa création, ou qu'on génère un identifiant aléatoire :

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 ('connexion');
INSERT INTO events (name) VALUES ('déconnexion');

SELECT * FROM events;

Quelques points importants à retenir :

L'expression est évaluée à chaque insertion, pas une seule fois au moment de la création de la table. Chaque ligne reçoit son propre timestamp et son propre jeton.
CURRENT_TIMESTAMP, CURRENT_DATE et CURRENT_TIME sont les trois mots-clés spéciaux qui n'ont pas besoin de parenthèses. Pour tout le reste, les parenthèses sont obligatoires.
L'expression ne peut pas faire référence à d'autres colonnes ni contenir de sous-requêtes — elle doit être autonome.

Si vous voulez qu'une colonne soit facultative mais horodatée automatiquement quand elle est renseignée, retirez le NOT NULL et gardez la valeur par défaut. Si vous la voulez obligatoire et horodatée automatiquement, mettez les deux.

DEFAULT NULL : autorisé (et parfois utile)

Écrire DEFAULT NULL revient exactement au même que ne pas définir de valeur par défaut du tout — la colonne vaut NULL si vous ne fournissez pas de valeur. Cela reste pertinent quand vous voulez indiquer explicitement dans le schéma que « aucune valeur » est bien l'état initial souhaité :

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;

bio et avatar se comportent exactement de la même façon ici. Le DEFAULT NULL sur bio joue le rôle de commentaire écrit en code : il signale à quiconque lit le schéma que l'absence de bio est un état tout à fait normal, et non un oubli.

Ajouter une contrainte NOT NULL sur une table existante

C'est là que ça devient un peu délicat. La commande ALTER TABLE de SQLite est volontairement limitée — impossible de faire un ALTER COLUMN ... SET NOT NULL comme sous Postgres. Ce que vous pouvez faire dépend du fait que la colonne existe déjà ou non.

Pour une nouvelle colonne, ADD COLUMN ... NOT NULL fonctionne, mais vous devez fournir une valeur par défaut. Sinon, les lignes existantes se retrouveraient d'un coup avec NULL dans une colonne NOT NULL, ce qui est tout simplement impossible :

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

-- Fonctionne : les lignes existantes reçoivent la valeur par défaut.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

Sans la valeur par défaut, vous obtiendrez une erreur :

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Erreur d'exécution : Impossible d'ajouter une colonne NOT NULL avec une valeur par défaut NULL

Pour une colonne déjà existante, pas de modification en place possible. La méthode classique, c'est la fameuse danse de la reconstruction : on crée une nouvelle table avec la contrainte qu'on veut, on copie les données, on supprime l'ancienne, puis on renomme la nouvelle. On verra tout ça en détail dans la page drop-and-alter-table — pour l'instant, retenez simplement que cette limitation est bien réelle et qu'il faut concevoir votre schéma en conséquence.

Un exemple concret combinant les deux

En production, la plupart des tables combinent les deux contraintes pour traduire « ce que l'application considère comme vrai » :

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;

Lisez ce schéma de haut en bas et vous devinerez ce que fait l'application sans avoir vu une seule ligne de code. customer est obligatoire et n'a aucun repli : l'appelant doit savoir à qui appartient la commande. Le montant, la devise et le statut ont tous des valeurs par défaut sensées, ce qui fait que même l'insertion la plus minimale produit une ligne cohérente. notes est facultatif. created_at est rempli par la base, et c'est bien le seul endroit où il devrait l'être.

C'est tout l'intérêt de ces contraintes : elles transforment des hypothèses implicites en règles que la base de données fait elle-même respecter.

Les pièges classiques

Une petite liste des trucs qui font trébucher pas mal de monde :

Un NULL explicite court-circuite DEFAULT. INSERT INTO t (col) VALUES (NULL) n'utilisera pas la valeur par défaut. Il faut que la colonne soit carrément absente de la liste des colonnes.
Les expressions par défaut exigent des parenthèses. DEFAULT CURRENT_TIMESTAMP fonctionne (c'est l'un des trois mots-clés spéciaux). DEFAULT lower(hex(randomblob(8))), non — il faut l'envelopper : DEFAULT (lower(hex(randomblob(8)))).
NOT NULL et la chaîne vide, ce n'est pas pareil. '' est une valeur TEXT parfaitement valide et ne déclenchera pas la contrainte. Si vous voulez aussi interdire les chaînes vides, c'est le boulot de CHECK (page suivante).
ADD COLUMN ... NOT NULL exige un DEFAULT qui ne soit pas NULL. Sans ça, SQLite refuse la modification.

La suite : les contraintes CHECK

NOT NULL et DEFAULT couvrent les cas « doit exister » et « à remplir si absent ». Pour la couche de validation suivante — « doit être positif », « doit faire partie de cette liste de valeurs », « la date de fin doit être postérieure à la date de début » — SQLite propose les contraintes CHECK, qui vous permettent d'écrire n'importe quelle expression booléenne que chaque ligne devra satisfaire. C'est l'objet de la page suivante.

Questions fréquentes

Comment rendre une colonne obligatoire en SQLite ?

Il suffit d'ajouter NOT NULL à la définition de la colonne : email TEXT NOT NULL. Tout INSERT ou UPDATE qui laisse cette colonne à NULL échoue avec l'erreur NOT NULL constraint failed. Combine-le avec un DEFAULT si vous voulez une valeur de repli quand l'appelant n'en fournit pas.

Comment fonctionnent les valeurs par défaut en SQLite ?

DEFAULT <valeur> indique à SQLite quelle valeur utiliser quand un INSERT ne précise rien pour cette colonne. Le défaut peut être un littéral (DEFAULT 0, DEFAULT 'pending'), NULL, ou une expression entre parenthèses du genre DEFAULT (CURRENT_TIMESTAMP) ou DEFAULT (lower(hex(randomblob(8)))). Les défauts à base d'expression sont réévalués à chaque insertion.

Pourquoi SQLite renvoie 'NOT NULL constraint failed' à l'insertion ?

Parce que vous insérez une ligne sans fournir de valeur pour une colonne NOT NULL qui n'a pas de DEFAULT. Trois options : inclure la colonne dans votre INSERT, lui donner un DEFAULT, ou relâcher la contrainte. Attention, passer explicitement NULL déclenche aussi l'erreur — NOT NULL rejette les valeurs nulles, peu importe d'où elles viennent.

Peut-on ajouter NOT NULL à une colonne existante en SQLite ?

Pas directement : ALTER TABLE ... ALTER COLUMN n'existe pas en SQLite. Soit vous ajoutez une nouvelle colonne avec NOT NULL DEFAULT <valeur> (le défaut est obligatoire pour les lignes existantes), soit vous reconstruisez la table : vous en créez une nouvelle avec la contrainte, vous copiez les données, vous supprimez l'ancienne et vous renommez la nouvelle.

Concepts liés

SQLite NOT NULL et DEFAULT : contraintes de colonnes