Ordre d'un DELETE CASCADE

Version imprimable

19/12/2011, 17h39
Invité

Ordre d'un DELETE CASCADE

Bonjour,

J'aimerai savoir si on peut contrôler l'ordre dans lequel SQL serveur gère les règles "ON DELETE CASCADE" lorsque qu'une table mère est liée à plusieurs tables filles elles-même liées entres elles.

Un exemple :
- Soit une table T0
- Soit une table T1 ayant une clé étrangère vers T0, avec suppression en cascade
- Soit une table T2 ayant une clé étrangère vers T0, avec suppression en cascade, et une clé étrangère vers T1, sans suppression en cascade.

Je souhaite supprimer une ligne de T0.
Normalement, grâce à mes cascades, SQL Server devrait supprimer les lignées liées dans les tables T1 et T2.
Deux scénario sont possible :
- Suppression de la ligne de T2
- Suppression de la ligne de T1
=> SUCCESS

- Suppression de la ligne de T1
=> ECHEC car T2 pointe vers T1

Donc ma question est : puis-je choisir l'ordre dans lequel SQL Serveur repercute les suppressions en cascade sur les tables filles ?

C'est un peu compliqué, j'espère que j'ai été assez clair !
Merci d'avance.
19/12/2011, 22h58
SQLpro

Votre question est idiote. Sachant qu'un ordre SQL est une transaction à part entière, soit tout sera supprimé, soit rien ne le sera. Il n'y a pas de demi mesure et l'ordre n'a donc aucune importance.

A +
20/12/2011, 09h21
Invité

Votre réponse l'est tout autant !

En gros ce que vous me dites, c'est que "ou bien ça marchera, ou bien sa me marchera pas". C'est une remarque pleine de profondeur, mais ça me fais de belles jambes.

Je reformule donc ma question : comme puis-je m'assurer que ça marche à tous les coups ?
20/12/2011, 11h39
SQLpro
Parce que c'est une transaction et que les transaction fonctionnent de manière atomique, c'est à dire en tout ou rien.
- Soit les contraintes permettent de supprimer toutes les informations de toutes les tables cascadées
- Soit une seule contrainte empêche de supprimer une seule ligne et dans ce cas rien n'est supprimé.
Bref, il serait temps pour vous de vous former su ce que sont les transactions dans les SGBDR !
A lire donc :
Les techniques des SGBDR
Transactions et niveau d'isolation

A +
20/12/2011, 13h56
Invité

Vous seriez aimable d'arrêter de me prendre de haut.
Vous n'avez toujours pas répondu à ma question : mon sujet demande "comment faire", et vous y répondez en disant "parce-que".

Je ne vous parle pas de transaction mais du fonctionnement du "on delete cascade". Le fait que tout cela fasse parti d'une transaction est très interessant, mais ça n'est pas de ça que je parle, ce que vous verriez si vous preniez le temps de lire vraiment ma question.

Vous êtes donc à coté de la plaque. Merci donc de me répondre vraiment et d'arrêter de me donner des leçons stupides !
20/12/2011, 15h05
Oishiiii

Citation:

Envoyé par darnold

Donc ma question est : puis-je choisir l'ordre dans lequel SQL Serveur repercute les suppressions en cascade sur les tables filles ?

Non ce n'est pas possible.

J'ai fait quelque tests, ça fonctionne sans soucis.
SQL Server semble se débrouiller tout seul pour gérer ce genre de références "circulaires" (ou en tout cas plusieurs chemins de cascade et dépendances).
Donc rien à faire.

Sinon si vous n'êtes pas convaincu désactivez la FK sur t2 référencant t1 avant la suppression dans t0 :

Code:

ALTER TABLE t2 NOCHECK CONSTRAINT FK_t2_vers_t1

Ou alors, enlevez les CASCADE et utilisez un trigger INSTEAD OF pour faire les suppressions vous même.
20/12/2011, 15h53
Invité

Citation:

SQL Server semble se débrouiller tout seul pour gérer ce genre de références "circulaires" (ou en tout cas plusieurs chemins de cascade et dépendances).
Donc rien à faire.

Ok, j'avais bien noté que il semblait se débrouiller tout seul, mais je ne savais pas si c'était un coup de bol ou s'il était capable de gérer le bazar tout seul.

Sinon la solution du "ALTER TABLE t2 NOCHECK CONSTRAINT FK_t2_vers_t1" me parrait bonne aussi.

Je passe le sujet en résolu.
Merci !
10/11/2016, 12h28
DukeMan

Bonjour,

Cette discussion n'est certes pas récente mais elle correspond en tout point au problème de j'ai eu, à savoir un delete sur une table principale qui delete donc en cascade d'autres tables mais mon souci est qu'une des ces tables exécute un trigger qui met à jour une table précédemment touchée par le delete en cascade, ce qui me fait un beau plantage. Vous me direz, la désactivation du trigger puis sa réactivation peuvent suffire, oui c'est vrai.
J'ai donc utilisé le plan d'exécution sur ma BDD : l'ordre des delete suit l'ordre alphabétique selon le nom des tables. Trouvant cela étonnamment suspect, et par curiosité, j'ai fait différents tests pour en savoir plus.

J'ai alors fait le test suivant :
1 - Création d'une table T2
2 - Création d'une table T4
3 - Création d'une table T3
4 - Création d'une table T1
5 - Création d'une contrainte de T4 vers T1
6 - Création d'une contrainte de T2 vers T1
7 - Création d'une contrainte de T3 vers T1

Quand je fais un delete sur T1, l'ordre des tables touchées par la cascade est T4 puis T2 puis T3.
Si je supprime la contrainte des T2 vers T1 et que je la recrée l'ordre devient T4 puis T3 puis T2 : la cascade de delete suit donc l'ordre de création des contraintes indépendamment de l'ordre de création des tables. Cela peut être utile, ...
10/11/2016, 14h14
escartefigue

Citation:

Envoyé par DukeMan

Quand je fais un delete sur T1, l'ordre des tables touchées par la cascade est T4 puis T2 puis T3.
Si je supprime la contrainte des T2 vers T1 et que je la recrée l'ordre devient T4 puis T3 puis T2 : la cascade de delete suit donc l'ordre de création des contraintes indépendamment de l'ordre de création des tables. Cela peut être utile, ...

Je doute fort que l'on puisse ériger en règle ce que vous constatez avec un test unique sur un jeu de tables unique !
D'ailleurs, n'y a-t- il pas des versions SQL server qui traitent les DELETE en multi-threading ? auquel cas...
14/11/2016, 18h34
SQLpro

Citation:

Envoyé par DukeMan

Bonjour,

Cette discussion n'est certes pas récente mais elle correspond en tout point au problème de j'ai eu, à savoir un delete sur une table principale qui delete donc en cascade d'autres tables mais mon souci est qu'une des ces tables exécute un trigger qui met à jour une table précédemment touchée par le delete en cascade, ce qui me fait un beau plantage. Vous me direz, la désactivation du trigger puis sa réactivation peuvent suffire, oui c'est vrai.
J'ai donc utilisé le plan d'exécution sur ma BDD : l'ordre des delete suit l'ordre alphabétique selon le nom des tables. Trouvant cela étonnamment suspect, et par curiosité, j'ai fait différents tests pour en savoir plus.

J'ai alors fait le test suivant :
1 - Création d'une table T2
2 - Création d'une table T4
3 - Création d'une table T3
4 - Création d'une table T1
5 - Création d'une contrainte de T4 vers T1
6 - Création d'une contrainte de T2 vers T1
7 - Création d'une contrainte de T3 vers T1

Quand je fais un delete sur T1, l'ordre des tables touchées par la cascade est T4 puis T2 puis T3.
Si je supprime la contrainte des T2 vers T1 et que je la recrée l'ordre devient T4 puis T3 puis T2 : la cascade de delete suit donc l'ordre de création des contraintes indépendamment de l'ordre de création des tables. Cela peut être utile, ...

Le seul ordre qui est suivi, c'est l'ordre dans lequel l'optimiseur décide d'optimiser la suppression lorsque le DELETE en cascade est multi branche.....

En règle général pour un delete l'ordre n'a pas de conséquence majeur sur les performances puisque au globale, le volume sera le même est la durée identique quelque soit le bout que l'on prend.

A +