SQLite : DROP TABLE et ALTER TABLE expliqués

Les schémas évoluent. SQLite vous laisse les modifier — en grande partie.

Une fois qu'une table existe, tôt ou tard vous voudrez la renommer, ajouter une colonne, en supprimer une, ou carrément revoir toute sa structure. SQLite gère les cas courants directement avec DROP TABLE et ALTER TABLE, et propose une méthode de contournement documentée pour tout le reste.

Le hic : le ALTER TABLE de SQLite est nettement plus limité que celui de Postgres ou MySQL. Savoir ce qu'il sait faire — et ne sait pas faire — ainsi que le pattern de reconstruction pour les cas non couverts, c'est l'essentiel du métier ici.

Supprimer une table SQLite avec DROP TABLE

DROP TABLE supprime la table, ses lignes, ses index et tous les triggers qui y sont rattachés. Et il n'y a pas de retour en arrière :

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporaire'), ('aussi temporaire');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

La table a disparu. Si vous essayez de la requêter maintenant, vous obtenez l'erreur no such table: scratch.

Si vous n'êtes pas sûr que la table existe — un cas classique dans les scripts d'initialisation — ajoutez IF EXISTS pour que l'instruction ne fasse rien sans broncher quand la table est absente :

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'les deux suppressions ont réussi' AS result;

Sans IF EXISTS, le second DROP planterait. Avec, les deux passent sans souci.

Les clés étrangères peuvent bloquer un DROP

Si la vérification des clés étrangères est activée (PRAGMA foreign_keys = ON;) et qu'une autre table référence celle que vous voulez supprimer, le DROP échoue :

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

Plusieurs options s'offrent à vous : supprimer d'abord la table qui référence, effacer les lignes référencées, ou alors déclarer la clé étrangère avec ON DELETE CASCADE dès la création. SQLite ne va pas casser silencieusement l'intégrité référentielle à votre place.

ALTER TABLE : les quatre opérations possibles

En SQLite, ALTER TABLE ne gère que quatre opérations, ni plus ni moins :

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

-- 1. Renommer la table
ALTER TABLE posts RENAME TO articles;

-- 2. Renommer une colonne
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Ajouter une colonne
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Supprimer une colonne (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Chaque instruction s'exécute d'un seul coup. Les deux premières sont quasi gratuites : elles ne touchent qu'au schéma. ADD COLUMN est lui aussi rapide, car SQLite ne réécrit pas la table, il se contente d'enregistrer la nouvelle définition de colonne. En revanche, DROP COLUMN coûte plus cher : SQLite doit réécrire chaque ligne pour effacer physiquement les données de la colonne.

ADD COLUMN avec une valeur par défaut

Quand on ajoute une colonne à une table existante, elle vaut NULL pour toutes les lignes déjà présentes, sauf si on précise une valeur par défaut :

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

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

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Les deux lignes existantes reçoivent la valeur 'active'. La valeur par défaut doit être une constante — SQLite refuse CURRENT_TIMESTAMP ou toute autre expression non constante comme valeur par défaut dans un ADD COLUMN, parce qu'il lui faut une valeur applicable à chaque ligne existante sans avoir à l'évaluer ligne par ligne.

Si vous avez besoin d'un NOT NULL sans valeur par défaut, il faudra d'abord ajouter la colonne en nullable, la remplir avec un UPDATE, puis reconstruire la table pour poser la contrainte. Ce qui nous amène justement aux limites.

Ce que ALTER TABLE ne sait pas faire sous SQLite

Voici ce qui fonctionne sous Postgres ou MySQL, mais pas sous SQLite :

• Changer le type d'une colonne (ALTER COLUMN ... TYPE ...).
• Modifier la valeur par défaut d'une colonne sur place.
• Ajouter ou retirer NOT NULL, CHECK, UNIQUE ou PRIMARY KEY sur une colonne existante.
• Ajouter une clé étrangère à une colonne existante.
• Réorganiser l'ordre des colonnes.

Toute tentative renvoie une erreur de syntaxe. SQLite ne possède tout simplement pas de clause ALTER COLUMN. Et la réponse officielle est la même dans tous les cas : reconstruire la table.

Le motif de reconstruction de table

Quand ALTER TABLE ne peut pas faire ce qu'il faut, on crée une nouvelle table avec le schéma souhaité, on copie les données, on supprime l'ancienne, puis on renomme la nouvelle à sa place. Le tout dans une transaction, pour que ce soit du tout ou rien :

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

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

users.age est désormais un entier assorti d'une contrainte CHECK, et email est passé en NOT NULL. Les données ont suivi sans broncher.

Quelques points à garder en tête quand vous appliquerez cette recette en production :

• Désactivez les clés étrangères le temps de l'opération. Si d'autres tables font référence à la vôtre, lancez PRAGMA foreign_keys = OFF; avant la transaction, puis PRAGMA foreign_keys = ON; après. Sinon le DROP TABLE échouera. Attention : ce pragma ne peut pas être modifié à l'intérieur d'une transaction, il faut donc le positionner en dehors.
• Recréez les index et les triggers. Quand vous supprimez l'ancienne table, ses index et ses triggers partent avec elle. Pensez à les remettre sur la nouvelle table après le renommage.
• Vérifiez les vues. Les vues qui référencent la table conservent l'ancien nom dans leur SQL stocké. Reconstruisez celles qui dépendent des colonnes modifiées.

Ce schéma de reconstruction est verbeux, mais fiable. C'est exactement ce que font des outils de migration comme Alembic ou Rails en coulisses lorsqu'ils ciblent SQLite.

Supprimer plusieurs tables SQLite

Il n'existe pas d'instruction unique pour supprimer plusieurs tables d'un coup — vous exécutez un DROP TABLE pour chacune. Dans une transaction si vous voulez les regrouper :

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Les regrouper dans une transaction garantit que les trois DROP réussissent ensemble, ou aucun. Pratique quand on démantèle des tables liées entre elles qui pourraient échouer en cours de route à cause des clés étrangères.

Ce qu'il faut retenir

• DROP TABLE supprime une table avec ses index et ses triggers. Utilisez IF EXISTS pour des scripts idempotents.
• ALTER TABLE ne sait faire que quatre choses sous SQLite : renommer une table, renommer une colonne, ajouter une colonne, supprimer une colonne.
• Pour tout le reste — changer un type, ajouter une contrainte, poser une clé étrangère sur une colonne existante — il faut reconstruire la table dans une transaction.
• Attention aux clés étrangères, index, triggers et vues lors de la reconstruction. Ils ne suivent pas les données automatiquement.

La suite : insérer des données

Tout un chapitre passé sur les tables et les contraintes qui les structurent. Place au remplissage : le prochain chapitre démarre avec INSERT, y compris la forme multi-lignes, les valeurs par défaut, et la façon dont SQLite gère les insertions qui entrent en conflit avec vos contraintes.