Contrainte UNIQUE en SQLite : colonnes, clés composites, NULL

La contrainte UNIQUE en SQLite : interdire les doublons

Une contrainte UNIQUE indique à SQLite qu'une colonne (ou un groupe de colonnes) ne peut pas contenir deux fois la même valeur. C'est ce qui vous permet de garantir que « deux utilisateurs ne peuvent pas partager la même adresse e-mail » ou qu'« un code produit n'apparaît qu'une seule fois ».

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 Deux');

SELECT * FROM users;

Le troisième INSERT échoue avec l'erreur UNIQUE constraint failed: users.email. SQLite vérifie la contrainte à chaque écriture et refuse tout ce qui créerait un doublon. Les deux premières lignes sont bien enregistrées ; la troisième, elle, ne passe jamais.

En coulisses, la contrainte unique sqlite repose sur un index unique — exactement la même structure que celle utilisée par SQLite pour accélérer les recherches. Du coup, la vérification ne coûte presque rien, et la colonne se retrouve automatiquement indexée.

Syntaxe au niveau colonne ou au niveau table

UNIQUE peut s'écrire de deux façons : directement à côté de la colonne, ou comme clause séparée à la fin de la définition de la table :

-- Niveau colonne : simple, une seule colonne.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Niveau table : même effet, emplacement différent.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

Pour une seule colonne, les deux formes sont équivalentes — choisissez celle que vous trouvez la plus lisible. En revanche, dès que l'unicité doit porter sur plusieurs colonnes, la forme au niveau de la table devient indispensable.

Contrainte unique sur plusieurs colonnes en SQLite

Il arrive qu'une colonne ne soit pas unique en elle-même, mais qu'une combinaison de colonnes doive l'être. Un utilisateur peut s'inscrire à plusieurs cours, et un cours peut accueillir plusieurs utilisateurs — par contre, le couple (user_id, course_id) ne doit jamais apparaître deux fois :

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, cours différent
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- ok, utilisateur différent
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- échoue : la paire existe déjà

SELECT * FROM enrollments;

La contrainte porte sur la paire, pas sur chaque colonne prise isolément. L'utilisateur 1 peut s'inscrire à plusieurs cours, le cours 100 peut accueillir plusieurs utilisateurs — mais chaque combinaison ne peut apparaître qu'une seule fois.

C'est le pattern classique pour les tables de jointure dans les relations many-to-many.

UNIQUE vs PRIMARY KEY en SQLite

Les deux se ressemblent et sont liés, mais il ne faut pas les confondre :

• Une table a au maximum une PRIMARY KEY. En revanche, elle peut avoir plusieurs contraintes UNIQUE.
• La PRIMARY KEY représente l'identité de la ligne — c'est ce que les clés étrangères référencent, et c'est l'alias du rowid.
• UNIQUE veut juste dire « cette valeur (ou cette combinaison) ne se répète pas ».
• Dans une table classique, une colonne UNIQUE accepte les valeurs NULL ; une PRIMARY KEY, non (à une exception historique près qu'on laissera de côté).

Une structure courante :

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- identité interne, ne change jamais
    email    TEXT NOT NULL UNIQUE,  -- identifiant visible par l'utilisateur, doit être unique
    username TEXT NOT NULL UNIQUE   -- un autre identifiant unique
);

id, c'est ce que le reste de la base référence. Si email et username sont en UNIQUE, c'est parce que l'application l'impose — pas parce qu'ils servent d'identité. Quand un utilisateur change son adresse mail, son id ne bouge pas. C'est précisément pour ça qu'on les sépare.

Le piège des NULL avec UNIQUE

Voilà le truc qui surprend tout le monde la première fois. En SQLite, une colonne UNIQUE accepte autant de valeurs NULL que vous voulez :

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');  -- celle-ci échoue

SELECT * FROM invites;

Trois NULL, aucun souci. Deux 'ada@example.com', et là c'est le conflit.

La raison ? En SQL, NULL veut dire « inconnu », et deux valeurs inconnues ne sont pas considérées comme égales — du coup, la vérification d'unicité ne peut pas conclure qu'il y a un doublon. Si vous voulez au maximum un seul NULL, le plus propre est de combiner NOT NULL UNIQUE. Si les NULL sont autorisés mais qu'on n'en veut qu'un seul par combinaison avec les autres colonnes, c'est l'index partiel qu'il vous faut (on en reparle dans le chapitre sur les index).

Gérer les conflits avec ON CONFLICT

Par défaut, une violation de contrainte UNIQUE fait échouer l'instruction. Mais parfois, on préfère un autre comportement : remplacer la ligne existante, ignorer la nouvelle, ou mettre à jour certaines colonnes. SQLite propose deux façons de le faire.

La première s'écrit directement dans la définition de la contrainte avec 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');   -- remplace le premier
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

La deuxième fois qu'on insère theme, la ligne existante est supprimée et la nouvelle prend sa place. Les autres options disponibles sont IGNORE (qui ignore silencieusement), ABORT (le comportement par défaut), FAIL et ROLLBACK.

La seconde approche se fait au niveau de la requête, avec la syntaxe upsert — généralement plus souple, puisqu'elle permet de mettre à jour des colonnes précises :

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;

Le premier INSERT crée la ligne. Les deux suivants déclenchent la contrainte UNIQUE et basculent sur la branche DO UPDATE, qui incrémente count. C'est le pattern d'upsert INSERT ... ON CONFLICT — on lui consacre une page entière plus loin.

Contrainte UNIQUE ou index UNIQUE : quelle différence ?

CREATE UNIQUE INDEX rend exactement le même service qu'une contrainte UNIQUE. D'ailleurs, quand vous déclarez une contrainte UNIQUE, SQLite crée un index unique en coulisses — c'est le même mécanisme, juste avec un autre habillage.

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');  -- échoue

Quand privilégier l'un ou l'autre :

• Contrainte quand l'unicité fait partie intégrante de la définition de la table. Elle est documentée juste à côté des colonnes concernées.
• Index unique quand vous voulez un index partiel (avec une clause WHERE), un nom précis, ou que vous voulez l'ajouter à une table déjà existante sans la recréer. Sous SQLite, ALTER TABLE ne sait pas ajouter une contrainte, mais on peut toujours rajouter un index.

Côté écriture, le comportement est strictement identique. Le choix dépend surtout de l'endroit où vous voulez que la règle vive dans votre schéma.

Ajouter une contrainte UNIQUE à une table existante

ALTER TABLE est volontairement limité dans SQLite — il n'existe pas de ALTER TABLE ... ADD CONSTRAINT. Vous avez donc deux options en pratique :

-- Option 1 : ajouter un index unique. Fonctionne sur les tables existantes, même contrainte.
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');  -- échoue maintenant

Option 2 — quand vous tenez vraiment à inscrire la clause UNIQUE directement dans la définition de la table — passe par la fameuse danse de réécriture : créer une nouvelle table avec la contrainte, recopier les données, supprimer l'ancienne, puis renommer. On détaille ça dans la page suivante.

Petit avertissement : si vous essayez d'ajouter une contrainte d'unicité sur une colonne qui contient déjà des doublons, le CREATE UNIQUE INDEX va échouer. Nettoyez d'abord les doublons, puis créez l'index.

Décrypter l'erreur quand UNIQUE échoue

Le message d'erreur indique précisément quelle contrainte a sauté :

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

La première forme est une contrainte sur une seule colonne users.email. La seconde est composite — les deux colonnes sont listées parce que c'est leur combinaison qui existe déjà. Quand cette erreur apparaît :

1. Repérez la ligne qui contient déjà la valeur en conflit (SELECT ... WHERE email = '...').
2. Décidez de la suite : mettre à jour cette ligne, ignorer l'insertion, ou utiliser une autre valeur.
3. Si les doublons sont attendus et que vous voulez fusionner, passez à INSERT ... ON CONFLICT DO UPDATE.

L'erreur est volontairement bruyante : la plupart du temps, c'est exactement ce que vous voulez savoir. Des doublons qui passent en silence seraient bien pires qu'une écriture refusée.

La suite : supprimer et modifier des tables

On ne peut pas ajouter une contrainte UNIQUE sur une table existante avec un simple ALTER TABLE. C'est précisément cette limite qui oblige SQLite à passer par une chorégraphie particulière pour modifier un schéma — la réécriture de table. C'est le sujet de la page suivante, en même temps que les bases pour supprimer une table proprement.