truc2geek

2012/04/18

SQLite part 2 : intégrité référentielle

Filed under: SQLite — Étiquettes : , , , , , , — truc2geek @ 22:03

Intégrité référentielle

C’est le mécanisme interne à la Base de Données qui garantit la cohérence des données.

Concrètement, dans une BD contenant une table « clients » et une table « contrats », il serait illogique par exemple de pouvoir créer un contrat qui ne soit rattaché à aucun client, ou encore de pouvoir supprimer un client si au moins un contrat lui est rattaché.

Pour qu’une BD se comporte comme attendu, il est indispensable de bien définir les relations entre les données.

La clé primaire est l’identifiant d’une donnée, et la clé étrangère est l’identifiant de la donnée parente.
Pour reprendre l’exemple des sociétés et des contrats :

La table « société » contiendra les champs « id_societe », « nom » et « info ».
Le champ « id_societe » sera l’identifiant des enregistrements « société », la clé primaire.

La table « contrat » contiendra les champs « id_contrat », « id_societe » et « date_sign ».
Le champ « id_contrat » sera la clé primaire, et le champ « id_societe » sera la clé étrangère.
Chaque contrat reprendra dans le champ « id_societe », la valeur du champ « id_societe » de la société à laquelle il est rattaché.

premier essai

On ouvre la console et crée une base de données :
C:\SQLite\v-3-7-11\sqlite3 D:\BaseTest2.db

créer une table société :
CREATE TABLE T_Societe (
id_societe INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
nom VARCHAR(80) NOT NULL,
info VARCHAR(100)
);

créer une table contrat :
CREATE TABLE T_Contrat (
id_contrat INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
id_societe INTEGER NOT NULL,
date_sign DATE NOT NULL,
FOREIGN KEY (id_societe) REFERENCES T_Societe(id_societe)
);

créer deux enregistrements société
INSERT INTO T_Societe
(nom, info)
VALUES (‘AlimPlus’, ‘1er client’);
INSERT INTO T_Societe
(nom, info)
VALUES (‘Stock4’, ‘2e client’);

afficher le contenu de la table société :
SELECT * FROM T_Societe;
1:AlimPlus:1er client
2:Stock4:2e client

on retrouve bien nos deux sociétés, qui ont respectivement pour identifiant 1 et 2

créer un enregistrement contrat logique : lié à la société ‘AlimPlus’, qui a comme valeur pour le champ [id_societe] : 1
INSERT INTO T_Contrat
(id_societe, date_sign)
VALUES (1, ‘2012-03-14’);

créer maintenant un enregistrement illogique : un contrat rattaché à une société dont l’identifiant serait 5
INSERT INTO T_Contrat
(id_societe, date_sign)
VALUES (5, ‘2012-03-22’);
on n’a aucun message d’erreur, ce qui signifierait que cet enregistrement a été accepté ; vérifions :

SELECT * FROM T_Contrat;
1:1:2012-03-14
2:5:2012-03-22
on a la confirmation que cet enregistrement « contrat » illogique a été accepté par la BD : problème.

PRAGMA foreign_keys

En cherchant dans la documentation en ligne de SQLite, on arrive vite sur cette page.
La section 2, intitulée « Enabling Foreign Key Support » nous renseigne : depuis la version 3.6.19 de SQLite, les contraintes des clés étrangères peuvent être gérées, mais sont désactivées par défaut (pour faciliter la rétro-compatibilité).

on vérifie l’état actuel :
PRAGMA foreign_keys;
0
la gestion des contraintes de clés étrangères est désactivée.

Il suffit de l’activer ainsi :
PRAGMA foreign_keys = ON;

et pour vérifier que l’activation a bien été prise en compte :
PRAGMA foreign_keys;
1

on pourrait penser qu’il est inutile de vérifier que notre saisie a bien été prise en compte ; mais si on entre volontairement une commande contenant une faute, pour tester la réaction, on s’aperçoit que rien de particulier ne se passe : bien sûr la commande n’est pas reconnue, mais on n’en est pas averti…

essai concluant

on vide la table contrat :
DELETE FROM T_Contrat;

on vérifie :
SELECT * FROM T_Contrat;
ok, la table T_Contrat est bien vide.

on recrée le contrat lié à la société ‘AlimPLus’ :
INSERT INTO T_Contrat
(id_societe, date_sign)
VALUES (1, ‘2012-03-14’);

on vérifie le contenu de la table :
SELECT * FROM T_Contrat;
3:1:2012-03-14
ce contrat a maintenant comme identifiant (valeur du champ [id_contrat]) : 3

on teste l’enregistrement d’un contrat rattaché à une société inexistante, celle dont l’id_societe serait 5 :
INSERT INTO T_Contrat
(id_societe, date_sign)
VALUES (5, ‘2012-03-22’);
Error: foreign key constraint failed

on a un message d’erreur, on affiche le contenu de la table T_Contrat pour vérifier :
SELECT * FROM T_Contrat;
3:1:2012-03-14
L’enregistrement illogique a bien été refusé par la BD.

on tente de supprimer la deuxième société, ‘Stock4’ : cela devrait fonctionner, car aucun contrat n’y est rattaché :
DELETE FROM T_Societe WHERE id_societe=2;
pas de message d’erreur, vérifions le contenu de la table société :

SELECT * FROM T_Societe;
1:AlimPlus:1er client
la société ‘Stock4’ a bien été supprimée.

on tente maintenant de supprimer la première société, ‘AlimPlus’, à laquelle un contrat est rattaché :
DELETE FROM T_Societe WHERE id_societe=1;
Error: foreign key constraint failed

on affiche le contenu de la table pour vérifier que l’enregistrement n’a pas été supprimé :
SELECT * FROM T_Societe;
1:AlimPlus:1er client
ok, la suppression a bien été refusée.

on quitte :
.exit
exit

Publicités

Laisser un commentaire »

Aucun commentaire pour l’instant.

RSS feed for comments on this post. TrackBack URI

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Propulsé par WordPress.com.

%d blogueurs aiment cette page :