Partie I
SQL de base
60
© Éditions Eyrolles
Il suffit de relire le script de création de vos tables pour en déduire l’ordre d’insertion des enregistrements.
Cohérence du père vers le fils
En fonction des options choisies au niveau de la contrainte référentielle se trouvant dans la
table « fils » :
CONSTRAINT nomContrainte FOREIGN KEY … REFERENCES …
plusieurs scénarios sont possibles pour assurer la cohérence de la table « père » vers la table « fils » :
q
Prévenir la modification ou la suppression d’une clé primaire (ou candidate) de la table
« père ». Cette alternative est celle par défaut. Soit vous n’ajoutez pas d’option à la clause
REFERENCES – dans notre exemple, toutes les clés étrangères sont ainsi composées –, soit
vous utilisez NO ACTION pour les directives ON DELETE et ON UPDATE. La suppression
d’un avion n’est donc pas possible si ce dernier est référencé dans un affrètement.
q
Propager la suppression des enregistrements « fils » associés à l’enregistrement « père »
supprimé. Ce mécanisme est réalisé par la directive ON DELETE CASCADE. Dans notre exemple, nous pourrions ainsi décider de supprimer tous les affrètements dès qu’on retire un avion.
q
Étendre la modification de la clé primaire de l’enregistrement « père » aux enregistrements
« fils » associés. Ce mécanisme est réalisé par la directive ON UPDATE CASCADE. Dans
notre exemple, nous pourrions ainsi décider de mettre à jour tous les affrètements dès
qu’on modifie l’immatriculation d’un avion.
q
Propager l’affectation de la valeur nulle aux clés étrangères des enregistrements « fils »
associés à l’enregistrement « père » supprimé ou modifié. Ce mécanisme est réalisé par la
directive ON DELETE SET NULL (ou ON UPDATE SET NULL en cas de modification de
la clé primaire du « père »). Dans ces deux cas, il ne faut pas poser de contrainte NOT
NULL sur la clé étrangère. Dans notre exemple, nous pourrions ainsi décider de mettre
NULL dans la colonne compa de la table Pilote pour chaque pilote d’une compagnie
supprimée. Nous ne pourrions pas appliquer ce mécanisme à la table Affreter qui
dispose de contraintes NOT NULL sur ses clés étrangères (car composant la clé primaire).
RESTRICT est une directive de la norme SQL qui n’est pas encore mise en œuvre sous
MySQL. Elle concerne les SGBD compatibles avec les contraintes différées. Pour l’heure NO
ACTION (qui en principe diffère les contraintes) et RESTRICT (qui ne diffère pas les contraintes)
jouent le même rôle.
Les options DELETE CASCADE et DELETE SET NULL sont disponibles depuis la
version 3.23.50, celles relatives à ON UPDATE sont disponibles depuis la version 4.0.8.
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
SQL de base
60
© Éditions Eyrolles
Il suffit de relire le script de création de vos tables pour en déduire l’ordre d’insertion des enregistrements.
Cohérence du père vers le fils
En fonction des options choisies au niveau de la contrainte référentielle se trouvant dans la
table « fils » :
CONSTRAINT nomContrainte FOREIGN KEY … REFERENCES …
plusieurs scénarios sont possibles pour assurer la cohérence de la table « père » vers la table « fils » :
q
Prévenir la modification ou la suppression d’une clé primaire (ou candidate) de la table
« père ». Cette alternative est celle par défaut. Soit vous n’ajoutez pas d’option à la clause
REFERENCES – dans notre exemple, toutes les clés étrangères sont ainsi composées –, soit
vous utilisez NO ACTION pour les directives ON DELETE et ON UPDATE. La suppression
d’un avion n’est donc pas possible si ce dernier est référencé dans un affrètement.
q
Propager la suppression des enregistrements « fils » associés à l’enregistrement « père »
supprimé. Ce mécanisme est réalisé par la directive ON DELETE CASCADE. Dans notre exemple, nous pourrions ainsi décider de supprimer tous les affrètements dès qu’on retire un avion.
q
Étendre la modification de la clé primaire de l’enregistrement « père » aux enregistrements
« fils » associés. Ce mécanisme est réalisé par la directive ON UPDATE CASCADE. Dans
notre exemple, nous pourrions ainsi décider de mettre à jour tous les affrètements dès
qu’on modifie l’immatriculation d’un avion.
q
Propager l’affectation de la valeur nulle aux clés étrangères des enregistrements « fils »
associés à l’enregistrement « père » supprimé ou modifié. Ce mécanisme est réalisé par la
directive ON DELETE SET NULL (ou ON UPDATE SET NULL en cas de modification de
la clé primaire du « père »). Dans ces deux cas, il ne faut pas poser de contrainte NOT
NULL sur la clé étrangère. Dans notre exemple, nous pourrions ainsi décider de mettre
NULL dans la colonne compa de la table Pilote pour chaque pilote d’une compagnie
supprimée. Nous ne pourrions pas appliquer ce mécanisme à la table Affreter qui
dispose de contraintes NOT NULL sur ses clés étrangères (car composant la clé primaire).
RESTRICT est une directive de la norme SQL qui n’est pas encore mise en œuvre sous
MySQL. Elle concerne les SGBD compatibles avec les contraintes différées. Pour l’heure NO
ACTION (qui en principe diffère les contraintes) et RESTRICT (qui ne diffère pas les contraintes)
jouent le même rôle.
Les options DELETE CASCADE et DELETE SET NULL sont disponibles depuis la
version 3.23.50, celles relatives à ON UPDATE sont disponibles depuis la version 4.0.8.
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]