Intégrité référentielle

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:

CREATE TABLE services
(service_id CHAR(2) PRIMARY KEY,
 dénomination CHAR(25))
;

Cette table contient par exemple les données:

 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.
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.
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.
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: