SQLite et contrainte de clé étrangère

Introduction

SQLite est une bibliothèque C qui propose un moteur de base de données SQL embarquable dans une application.

Cette bibliothèque du domaine public qui fonctionne grâce à un simple fichier contenant la base de données et à un driver (C, C++, Python, PHP, PERL) connaît un franc succès dans le domaine de l’embarqué.

Logo SQLite

Problème

Outre certaines contraintes dues à son mode de fonctionnement (volume de données, accès concurrent, etc), SQLite ne supporte malheureusement pas la totalité des fonctionnalités décrites par la norme SQL-92 (ISO/CEI 9075:1992).

Parmi ces fonctionnalités non implémentées, on notera celle relative aux contraintes de clés étrangères, « foreign key constraints are parsed but are not enforced ».

Edit 11/09: Les clés étrangères sont supportées depuis la version 3.6.19, cette fonctionnalité est désactivée par défaut pour conserver la compatibilité (à activer avec PRAGMA foreign_keys = ON), pour plus de détails : http://sqlite.org/foreignkeys.html

Ceci se traduit par exemple, par la non vérification de l’intégrité relationnelle lors de l’insertion d’un tuple dans une table possédant une clé étrangère sur une seconde table.

Passons à la démonstration par l’exemple (le paquet sqlite3 est requis):

nico@laptop:~$ sqlite3 
SQLite version 3.5.9
sqlite> CREATE TABLE company (
   ...>   id INTEGER NOT NULL PRIMARY KEY,
   ...>   name VARCHAR(80) NOT NULL
   ...> );
sqlite> CREATE TABLE contact (
   ...>   id INTEGER NOT NULL PRIMARY KEY,
   ...>   name VARCHAR(100),
   ...>   comp_id INTEGER NOT NULL
   ...>   CONSTRAINT fk_company_id REFERENCES company(id)
   ...> );
sqlite> INSERT INTO company VALUES(1, "Google");
sqlite> SELECT *  FROM company;
id|name
1|Google
sqlite> INSERT INTO contact VALUES(1, "James Smith", 1);
sqlite> INSERT INTO contact VALUES(2, "John Doe", 2);
sqlite> SELECT * FROM contact;
id|name|comp_id
1|James Smith|1
2|John Doe|2

Dans cet exemple, le champ « comp_id » de la table « contact » possédant une contrainte d’intégrité relationnelle sur le champ « id » de la table « company », l’ajout du contact « John Doe » avec une référence à une société qui n’existe pas devrait provoquer une erreur.

Solution

Comme précisé dans la documentation, l’intégrité peut malgré tout être assurée par l’utilisation de triggers, qui vérifient, selon l’opération effectuée que celle-ci ne remet pas en cause l’intégrité de la base de données.

Ajoutons donc un trigger déclenché à l’insertion d’un nouveau tuple à la table contact :

sqlite> DELETE FROM contact where id = 2;
sqlite> SELECT * FROM contact;
id|name|comp_id
1|James Smith|1
sqlite> CREATE TRIGGER fki_contact_company_id
   ...> BEFORE INSERT ON contact
   ...> FOR EACH ROW BEGIN
   ...>   SELECT RAISE(ROLLBACK, 'insert on table contact violates foreign key constraint "fk_contact_company_id"')
   ...>   WHERE  NEW.comp_id IS NOT NULL
   ...>          AND (SELECT id FROM company WHERE id = new.comp_id) IS NULL;
   ...> END;
sqlite> INSERT INTO contact VALUES(2, "John Doe", 2);
SQL error: insert on table contact violates foreign key constraint "fk_contact_company_id"
sqlite> SELECT * FROM contact;
id|name|comp_id
1|James Smith|1

Si ce nouveau trigger permet de respecter la contrainte de clé étrangère entre les tables « company » et « contact » lors d’une insertion, il faut également prendre en compte les opérations de modification et de suppression de lignes.

Pour se faire, il faut créer deux triggers supplémentaires, se référer à la documentation qui aborde en détails les modalités de définition des procédures en fonction des différents cas.

Ressources

• site officiel de SQLite
• documentation sur l’assurance de l’intégrité grâce aux triggers
• générateur en ligne de triggers d’intégrité SQLite