4.2 Les contraintes d'intégrité référentielle sous SQLite

La plupart des SGBD relationnels offrent une fonctionnalité d'intégrité référentielle. Il s'agit d'un mécanisme qui assure que la valeur d'une clé étrangère dans une table détail correspond en tout temps à la clé primaire d'un enregistrement dans la table maître.

Exemple de contrainte d'intégrité référentielle

Dans l'exemple des étudiants qui étudient dans un établissement, la clé étrangère de chaque enregistrement dans la table etudiants contiendra toujours une valeur qui correspond à la clé primaire d'un enregistrement de la table etablissements.

Autrement dit, chaque étudiant pointera en tout temps vers un établissement qui existe.

Lorsqu'une contrainte d'intégrité référentielle est en place entre les étudiants et les établissements :

• Il est impossible d'ajouter un étudiant pour un établissement qui n'existe pas.
• Il est impossible de modifier un étudiant pour l'assigner à un établissement qui n'existe pas.
• Il est impossible de supprimer un établissement si au moins un étudiant lui est assigné.

Mise en place d'une contrainte d'intégrité référentielle dans DB Browser for SQLite

SQLite, comme la majorité des SGBD relationnels, offre un mécanisme d'intégrité référentielle.

Voici comment mettre en place les contraintes d'intégrité référentielles avec DB Browser for SQLite.

Une contrainte d'intégrité référentielle intervient toujours entre deux tables. Cependant, la contrainte doit être appliquée sur la table détail puisque c'est elle qui dépend de la table maître.

Lorsqu'on crée une nouvelle base de données, il est important de commencer par créer les tables qui ne contiennent aucune clé étrangère. Autrement dit, pour chaque relation, on doit créer la table maître avant la table détail.

Pour que la contrainte d'intégrité puisse être mise en place sur la table détail, il faut absolument que la clé primaire de la table maître soit UNIQUE ou mieux, PRIMARY KEY.

Lors de la création de la table détail, on précisera dans la colonne Clé étrangère de la clé étrangère le nom de la table maître ainsi que le nom de sa clé primaire.

Une fois la saisie terminée, la requête SQL affichée au bas de l'écran contiendra la clause FOREIGN KEY qui génèrera la contrainte.

Si vous ne voyez pas cette clause, vous devez cliquer sur un autre champ afin que l'ajout de la contrainte soit complété.

Ajouter une contrainte d'intégrité référentielle à une table existante

SQLite offre des fonctionnalités limitées pour modifier une table. Il n'est pas possible d'ajouter une contrainte d'intégrité référentielle sur une table existante à la ligne de commande SQLite.

Mais tout n'est pas perdu. DB Browser offre la possibilité d'effectuer à peu près toutes les modifications désirées. Comment est-ce possible ? Simplement parce qu'il se charge en arrière-plan de supprimer la table puis de la recréer.

Vous pouvez donc ajouter des contraintes d'intégrité référentielle à vos tables existantes à l'aide de DB Browser.

Erreur foreign key mismatch

Si, lors de l'ajout d'une contrainte, vous obtenez l'erreur « La modification de cette colonne a échoué. Erreur retournée par la base de données : foreign key mismatch », ceci pourrait être dû au fait que la clé primaire de la table maître n'a pas de clause PRIMARY KEY ni même UNIQUE.

Bogue avec clés étrangères NOT NULL

La version actuelle de DBBrowser pour Windows (3.10.1) contient un bogue important : il n'est pas possible d'ajouter un enregistrement dans une table qui contient une clé étrangère avec la clause NOT NULL.

Si cela vous arrive, vous avez deux options :

• Enlever la clause NOT NULL (attention : est-ce que c'est vraiment ce que vous désirez ?)
• Effectuer les INSERT à la ligne de commande SQLite.