Quand utiliser "ON UPDATE CASCADE" ?

Question

J'utilise régulièrement "ON DELETE CASCADE" mais je n'utilise jamais "ON UPDATE CASCADE" car je ne suis pas sûr de la situation dans laquelle cela sera utile.

Pour les besoins de la discussion, voyons un peu de code.

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

Pour "ON DELETE CASCADE", si un parent avec un id est supprimé, un enregistrement en enfant avec parent_id = parent.id sera automatiquement supprimé. Cela ne devrait pas poser de problème.

1. Cela signifie que "ON UPDATE CASCADE" fera la même chose lorsque id du parent est mis à jour ?

2. Si (1) est vrai, cela signifie qu'il n'est pas nécessaire d'utiliser "ON UPDATE CASCADE" si parent.id n'est pas actualisable (ou ne sera jamais actualisée) comme lorsqu'elle est AUTO_INCREMENT ou toujours réglé pour être TIMESTAMP . C'est bien ça ?

3. Si (2) n'est pas vrai, dans quel autre type de situation devrions-nous utiliser "ON UPDATE CASCADE" ?

4. Que se passe-t-il si je mets à jour (pour une raison ou une autre) le fichier child.parent_id pour être quelque chose qui n'existe pas, sera-t-il alors automatiquement supprimé ?

Je sais que certaines des questions ci-dessus peuvent être testées par programme pour comprendre, mais je veux aussi savoir si tout cela dépend du fournisseur de la base de données ou non.

Veuillez m'éclairer.

Answer 1

Il est vrai que si votre clé primaire n'est qu'une valeur d'identité auto incrémentée, vous n'auriez pas de réelle utilité pour ON UPDATE CASCADE.

Toutefois, disons que votre clé primaire est un code à barres UPC à 10 chiffres et qu'en raison de l'expansion, vous devez le remplacer par un code à barres UPC à 13 chiffres. Dans ce cas, ON UPDATE CASCADE vous permettra de modifier la valeur de la clé primaire et toutes les tables qui ont des références de clé étrangère à la valeur seront modifiées en conséquence.

En ce qui concerne le point 4, si vous changez l'ID de l'enfant en quelque chose qui n'existe pas dans la table parent (et que vous avez une intégrité référentielle), vous devriez obtenir une erreur de clé étrangère.

Answer 2

1. Oui, cela signifie que par exemple si vous faites UPDATE parent SET id = 20 WHERE id = 10 tous les enfants dont l'identifiant de parent est 10 seront également mis à jour à 20

2. Si vous ne mettez pas à jour le champ auquel la clé étrangère fait référence, ce paramètre n'est pas nécessaire.

3. Je ne vois pas d'autre utilisation.

4. Vous ne pouvez pas le faire car la contrainte de clé étrangère échouerait.

Answer 3

Je pense que vous avez bien cerné le problème !

Si vous suivez les meilleures pratiques en matière de conception de bases de données et que votre clé primaire n'est jamais modifiable (ce qui, à mon avis, devrait toujours être le cas de toute façon), alors vous n'avez jamais vraiment besoin de l'attribut ON UPDATE CASCADE clause.

Zed a soulevé un bon point, à savoir que si vous utilisez une naturel (par exemple, un champ ordinaire de votre table de base de données) comme clé primaire, il peut arriver que vous deviez mettre à jour vos clés primaires. Un autre exemple récent serait l'ISBN (International Standard Book Numbers) qui est passé de 10 à 13 chiffres + caractères il n'y a pas si longtemps.

Ce n'est pas le cas si vous choisissez d'utiliser l'option mère porteuse (par exemple, des clés générées artificiellement par le système) comme clé primaire (ce qui serait mon choix préféré dans toutes les occasions, sauf les plus rares).

Donc, en fin de compte : si votre clé primaire ne change jamais, alors vous n'avez jamais besoin de l'option ON UPDATE CASCADE clause.

Marc

Answer 4

Il y a quelques jours, j'ai eu un problème avec les déclencheurs, et j'ai découvert que ON UPDATE CASCADE peut être utile. Jetez un coup d'œil à cet exemple (PostgreSQL) :

CREATE TABLE club
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE band
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE concert
(
    key SERIAL PRIMARY KEY,
    club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
    band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
    concert_date DATE
);

Dans mon problème, j'ai dû définir des opérations supplémentaires (trigger) pour mettre à jour la table du concert. Ces opérations devaient modifier le club_name et le band_name. Je n'ai pas pu le faire, à cause de la référence. Je ne pouvais pas modifier le concert et ensuite traiter les tables des clubs et des groupes. Je ne pouvais pas non plus le faire dans l'autre sens. ON UPDATE CASCADE était la clé pour résoudre le problème.

Answer 5

Mon commentaire concerne principalement le point 3 : dans quelles circonstances ON UPDATE CASCADE est-il applicable si l'on suppose que la clé parentale n'est pas actualisable ? Voici un cas.

Je suis confronté à un scénario de réplication dans lequel plusieurs bases de données satellites doivent être fusionnées avec un maître. Chaque satellite génère des données sur les mêmes tables, de sorte que la fusion des tables avec le maître entraîne des violations de la contrainte d'unicité. J'essaie d'utiliser ON UPDATE CASCADE dans le cadre d'une solution dans laquelle je réincrémente les clés lors de chaque fusion. ON UPDATE CASCADE devrait simplifier ce processus en automatisant une partie du processus.