PostgreSQL est un SGBD moderne et puissant qui gère la notion d'intégrité référentielle des données. Cette notion a déjà été évoquée plusieurs fois dans ce blog et notamment dans le billet intitulé PostgreSQL (initiation).
Nous allons y revenir un peu plus en détails.
Considérons la table des employés d'une entreprise. Cette table contient une colonne "service" où est indiqué sous forme d'un code le service auquel appartient l'employé. D'autre part, nous avons une table "services", donnant la signification de ce code, créée avec cette instruction:
Pour cela, il suffit que la table des employés ait été créée postérieurement à la table "services" suivant par exemple cette instruction:
La colonne service de la table "employés" constitue une clef étrangère (foreign key) qui pointe vers la clef primaire de la table services.
Ici le champ "service" peut prendre la valeur NULL, ce qui correspondrait au cas où le service de l'employé n'est pas connu. Mais la table "employé" peut-être définie de manière à exclure cette possibilité.
Que se passe-t-il si nous essayons de supprimer un service contenant des employés?
Essayons de procéder dans un terminal psql.
La suppression est refusée:
C'est le comportement par défaut. Mais d'autres possibilités sont offertes par PostgreSQL. Le code service de tous les employés appartenant au service supprimé peut être mis à NULL . Ce sera le cas si l'instruction de création de la table "employés" contient :
Dans notre base de données de test nous avons également défini les tables paie et codepaie:
Schématiquement, nous avons cette situation:
Les relations d'intégrité référentielle sont matérialisées par des flèches issues des FK (foreign keys) et pointant vers les PK (primary keys). En rouge, nous avons une relation définie avec 'ON DELETE CASCADE'.
De ce fait, un "delete" au niveau de la table "services" va se propager vers la droite, telle une vague destructrice remontant un courant dont le sens est indiqué par les flèches. La table codepaie est à l'abri (la flèche est à l'envers). Mais qu'en est-il de la table paie? Logiquement elle devrait être protégée car la contrainte "paie => employés" n'a pas été définie de manière particulière (elle n'est pas représentée par une flèche rouge).
Vérifions tout cela dans notre terminal psql. Procédons avec un utilisateur ordinaire (titine) auquel nous donnons tous les droits sur la table services:
Pour les autres tables de bdtest, titine a le droit "select":
mais pas d'autres droits:
Recherchons tout d'abord quel est dans chaque service le nombre d'employés référencés par la table paie:
En fait, deux employés seulement sont concernés: un dans le service '00' et un autre dans le service '01'.
Essayons dans un premier temps de supprimer le service '02':
Opération couronnée de succès: une seule rangée supprimée dans la table "services".
Par contre dans la table "employés"
quatre rangées ont été supprimées alors que titine n'a pas le droit "delete" sur cette table!
Attaquons-nous maintenant au service '01':
La suppression d'un des 4 employés du groupe a échoué: celui référencé depuis la table paie. De ce fait malgré l'option CASCADE, la suppression du service devient impossible. Les autres employés du groupe, qui eux auraient pu être supprimés, sont sauvés:
Nous allons y revenir un peu plus en détails.
Considérons la table des employés d'une entreprise. Cette table contient une colonne "service" où est indiqué sous forme d'un code le service auquel appartient l'employé. D'autre part, nous avons une table "services", donnant la signification de ce code, créée avec cette instruction:
CREATE TABLE services
(service_id CHAR(2) PRIMARY KEY,
dénomination CHAR(25))
;
Cette table contient par exemple les données:(service_id CHAR(2) PRIMARY KEY,
dénomination CHAR(25))
;
service_id | dénomination
------------+---------------------------
00 | Direction
01 | Secrétariat
02 | Gestion du personnel
03 | Relations publiques
04 | Production
05 | Recherches
06 | Ventes
PostgreSQL peut garantir que le code service de chaque employé se trouve dans la table "services", en clair chaque employé doit être affecté dans un service.------------+---------------------------
00 | Direction
01 | Secrétariat
02 | Gestion du personnel
03 | Relations publiques
04 | Production
05 | Recherches
06 | Ventes
Pour cela, il suffit que la table des employés ait été créée postérieurement à la table "services" suivant par exemple cette instruction:
CREATE TABLE employés
(matricule SERIAL PRIMARY KEY,
nom CHAR(50),
adresse CHAR(50),
code_postal CHAR(5),
cp_seq SMALLINT,
naissance DATE,
sexe CHAR(1) CHECK (sexe IN ('M','F')),
service CHAR(2) REFERENCES services)
;
qui définit (en rouge) une contrainte d'intégrité référentielle.(matricule SERIAL PRIMARY KEY,
nom CHAR(50),
adresse CHAR(50),
code_postal CHAR(5),
cp_seq SMALLINT,
naissance DATE,
sexe CHAR(1) CHECK (sexe IN ('M','F')),
service CHAR(2) REFERENCES services)
;
La colonne service de la table "employés" constitue une clef étrangère (foreign key) qui pointe vers la clef primaire de la table services.
Ici le champ "service" peut prendre la valeur NULL, ce qui correspondrait au cas où le service de l'employé n'est pas connu. Mais la table "employé" peut-être définie de manière à exclure cette possibilité.
Que se passe-t-il si nous essayons de supprimer un service contenant des employés?
Essayons de procéder dans un terminal psql.
La suppression est refusée:
C'est le comportement par défaut. Mais d'autres possibilités sont offertes par PostgreSQL. Le code service de tous les employés appartenant au service supprimé peut être mis à NULL . Ce sera le cas si l'instruction de création de la table "employés" contient :
service CHAR(2) REFERENCES services ON DELETE SET NULL
Cette table peut également être définie de telle sorte que la suppression d'un service entraîne la suppression de tous les employés. Ce sera le cas si le "CREATE" contient:service CHAR(2) REFERENCES services ON DELETE CASCADE
Examinons d'un peu plus près cette possibilité afin d'avoir une idée de ce qu'implique exactement le mot CASCADE.Dans notre base de données de test nous avons également défini les tables paie et codepaie:
CREATE TABLE codepaie
(code CHAR(2) PRIMARY KEY,
signification CHAR(20))
;
CREATE TABLE paie
(matricule INTEGER REFERENCES employés,
mois CHAR(6),
seq SMALLINT,
code CHAR(2) REFERENCES codepaie,
montant NUMERIC(13,2),
PRIMARY KEY (matricule,mois,seq))
;
La table paie se rapportant au paiement du salaire des employés, chaque opération de paiement doit faire référence à un employé donné. La table codepaie donne la signification des codes utilisés et du fait de la relation d'intégrité référentielle, elle définit également l'ensemble des codes autorisés.(code CHAR(2) PRIMARY KEY,
signification CHAR(20))
;
CREATE TABLE paie
(matricule INTEGER REFERENCES employés,
mois CHAR(6),
seq SMALLINT,
code CHAR(2) REFERENCES codepaie,
montant NUMERIC(13,2),
PRIMARY KEY (matricule,mois,seq))
;
Schématiquement, nous avons cette situation:
Les relations d'intégrité référentielle sont matérialisées par des flèches issues des FK (foreign keys) et pointant vers les PK (primary keys). En rouge, nous avons une relation définie avec 'ON DELETE CASCADE'.
De ce fait, un "delete" au niveau de la table "services" va se propager vers la droite, telle une vague destructrice remontant un courant dont le sens est indiqué par les flèches. La table codepaie est à l'abri (la flèche est à l'envers). Mais qu'en est-il de la table paie? Logiquement elle devrait être protégée car la contrainte "paie => employés" n'a pas été définie de manière particulière (elle n'est pas représentée par une flèche rouge).
Vérifions tout cela dans notre terminal psql. Procédons avec un utilisateur ordinaire (titine) auquel nous donnons tous les droits sur la table services:
Pour les autres tables de bdtest, titine a le droit "select":
mais pas d'autres droits:
Recherchons tout d'abord quel est dans chaque service le nombre d'employés référencés par la table paie:
En fait, deux employés seulement sont concernés: un dans le service '00' et un autre dans le service '01'.
Essayons dans un premier temps de supprimer le service '02':
Opération couronnée de succès: une seule rangée supprimée dans la table "services".
Par contre dans la table "employés"
quatre rangées ont été supprimées alors que titine n'a pas le droit "delete" sur cette table!
Attaquons-nous maintenant au service '01':
La suppression d'un des 4 employés du groupe a échoué: celui référencé depuis la table paie. De ce fait malgré l'option CASCADE, la suppression du service devient impossible. Les autres employés du groupe, qui eux auraient pu être supprimés, sont sauvés:
Post a Comment