Discussion :

[olra72]

Bonjour,

J'ai attentivement lu diverses contributions sur le sujet (ex. https://www.developpez.net/forums/d529939/bases-donnees) sans y trouver la solution à mon problème.

Sur une application pure-Access (Front-End et Back-End Access), j'ai défini une table t_MaTable, avec une clé primaire ID_MaTable (autoincrémentée) et une clé étrangère Pere_ID qui pointe sur une autre ligne de cette même table. Cela permet de définir une sorte de forêt où la racine de chaque chaque arbre est caractérisée par Pere_ID=Null. Sous Access, il me suffit de définir une relation de Pere_ID vers ID_MaTable avec effacement en cascade pour que, quand je détruis un nœud X, cela détruise tous les nœuds ayant X pour père, puis tous les nœuds ayant un de ces fils comme père, etc, etc. Cela permet de détruire avec un seul DELETE tout un arbre, ou tout une branche d'un arbre, ou une simple feuille.

Maintenant, j'essaie de porter tout ça sous SQL (MicroSoft SQL Server Manager 2016) et ça se corse ! Quand je définis la relation de Pere_ID vers ID_MaTable, le champ "Spécification INSERT et UPDATE / Règle de suppression" reste obstinément grisé - alors qu'il est disponible quand je définis une relation entre deux tables différentes.

Comment obtenir sous SQL ce que j'avais déjà sous Access ?

J'ai bien noté l'idée consistant à externaliser la relation Fils-Père dans une table de jointure ne contenant que deux clés étrangères (une vers le fils, une vers le père) mais ça multiplie les tables et j'ai une demi-douzaine de forêts distinctes à gérer (les analyses, les matériaux, les espaces de stockages, etc.) et ça me fait bondir de six à douze tables ... à moins que je regroupe toutes mes arborescences au sein d'une seule table en ajoutant une colonne "Nature" qui me permettre de retrouver tous les arbres ayant une nature commune (ou que je me serve du premier niveau comme un identifiant de cette fameuse "nature") ... ce qui me ferait redescendre de douze tables moyennes à deux grosses. Bref, je me tâte ...

Il y a d'autres trucs comme ça qui roulent sous Access et qui deviennent velus sous SQL ? Déjà que j'ai renoncé à utiliser les champs multi-valués, pourtant si pratiques à utiliser ...

Cordialement,
Olivier

PS : Je me suis dit que c'était une sécurité contre les boucles (car, après tout, mes Pere_ID pourraient bien pointer n'importe où dans la table, sans nécessairement définir une structure arborescente) mais, en cas de relations croisées entre deux tables, on peut aussi déclencher des DELETE en cascade qui finiraient par tout effacer ou par reboucler sur eux-mêmes, donc l'argument du "c'est plus sûr comme ça !" ne tient pas.

[SQLpro]

La raison pour laquelle ceci est interdit dans SQL Server est une raison de sécurité, d'efficacité et de performance.
Access n'est pas un SGBD Relationnel. Comme tout bon SGBD à base de fichiers, il n'a pas d'optimiseur ni de possibilité d'exécuter de manière centralisée des traitements tels déclencheurs UDF ou procédures stockées. Il ne sait pas non plus gérer des transactions et c'est pour cela qu'il ne les journalisent pas.

MS SQL Server est doté d'un journal de transactions qui fait que toute modification de la BD (que ce soit au niveau DDL : CREATE, ALTER, DROP..., DCL : GARNT, REVOKE... ou DML : INSERT, UPDATE, DELETE...) fait l'objet d'une journalisation : copie de l'instruction de mise à jour, copie des valeurs à modifier avant modification, etc...
En optant pour un DELETE CASCADE, la journalisation qui en résulte pourrait devenir tellement lourde que vous courreriez à la catastrophe, car pour supprimer récursivement toutes les lignes le temps de journalisation, comme la pose successive des verrous demanderait à dégager tous les utilisateurs et si vous avez de multiples clients connectés, pour pourriez vous retrouver devant une forte contention et des verrous mortels en pagaille !

C'est aussi pourquoi il existe d'autres modes comme le ON DELETE SET NULL ou le ON DELETE SET DEFAULT....

Pour les "champs"[1] multivalués même problème... Les performances ! C'est stupide d'utiliser du multivalué alors qu'une table est fait pour représenter différentes valeurs et est optimisée pour cela !

A +

[1] le terme "champ" n'existe pas dans le vocabulaire des SGBDR dans lesquels on parle de colonne. Les champs c'est pour les patates ou le chirurgien et les formulaires !

[olra72]

"Access n'est pas un SGBD Relationnel" ? Ça fait trois ans que je travaille avec des accdb, et des accde, six applications avec de cinq à dix accès simultanés. Mes applis ont entre 30 et 80 tables, jusqu'à 230 requêtes explicitement nommées et entre 10.000 et 25.000 lignes de VBA. Alors ça ne me donne pas trop l'impression de "ne pas être quelque chose" ... ou je ne comprends ce "qu'être quelque chose" veut dire.

Maintenant, que SQL fasse des choses en plus, pourquoi pas ? J'espère juste que cette fameuse journalisation ne va pas écrouler les performances. Ça serait amusant que des contraintes visant à améliorer les performances (avec 100 utilisateurs simultanés sur des tables de plus de 100.000 lignes ?) les dégrade quand il n'y a que trois utilisateurs travaillant sur des tables de 1000 lignes. Bon, je verrai vite ça à l'usage ... merci de l'avertissement !

Maintenant, concernant mes DELETE CASCADE sur les tables auto-référencées, pas d'autre proposition que l'externalisation des références ? J'ai déjà commencé à coder ça, juste pour voir, mais si quelqu'un a une autre idée ...

Cordialement,
Olivier

[aieeeuuuuu]

Bonjour,

Plutôt que de dégrader votre modèle, vous pouvez créer un déclencheur "instead of delete" , et effectuer la suppression des éléments demandés ainsi que de leurs enfants grâce à une requête récursive.

exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
 
 
create table T
(	id int not null primary key identity
	,pere int null
	,
	constraint FK_T_T foreign key (pere) references T(id) 
);
GO
 
CREATE TRIGGER TRG_IoD_T ON T
INSTEAD OF DELETE
 
AS
BEGIN
 
;WITH CTE AS (
	SELECT id 
	from DELETED
 
	union all 
 
	select T.id
	FROM CTE
	INNER JOIN T
		ON T.Pere = CTE.id
)
DELETE FROM T
from cte
inner join T
	on T.id = CTE.id
;
 
END
GO
 
 
insert into T (pere) VALUES(NULL);
insert into T (pere) VALUES(1);
insert into T (pere) VALUES(2);
insert into T (pere) VALUES(3);
insert into T (pere) VALUES(4);
insert into T (pere) VALUES(5);
insert into T (pere) VALUES(NULL);
insert into T (pere) VALUES(7);
insert into T (pere) VALUES(8);
GO
 
 
 
 
begin tran
select * from T;
delete from T where id = 4
select * from T;
 
rollback

[fsmrel]

Bonsoir,

La raison pour laquelle ceci est interdit dans SQL Server est une raison de sécurité, d'efficacité et de performance.

Bizarre, bizarre...

Point de vue tout à fait relatif et subjectif !

A l'inverse, DB2 for z/OS (depuis DB2 V2, 1988) a toujours autorisé (voire préconisé) ON DELETE CASCADE dans le cas des tables qui s’auto-référencent !
J'ai eu à pratiquer ça (à mon corps défendant) sur des très grosses tables, et pas de problème !

[olra72]

Intéressant tout ça ! Merci à tous ! .. même à SQLPro qui est - effectivement - un rien dogmatique (mais ça nous arrive à tous, hein ?).

Côté "performances de développement", les champs multivalués, ça dépote. Sur de très très grosses tables, ou pour faire du multivalué dynamique, je dis pas, mais, comme souvent, tout dépend du contexte. Quand on fait de "l'AGILE" (ou du RAD), avec une à deux versions par semaine, on se met à apprécier. Bien sûr, pour ceux qui se sont occupés de gros monstres du genre de "Louvois" (avec le succès que l'on sait), ce genre de pratique vous mène direct au bucher.

Sinon, j'ai fait un essai pour externaliser les références Père-Fils dans une table à part (à partir d'une table initialement auto-référencée) et - surprise - les effacements en cascade ne sont plus possibles ! Quand on efface un objet, ça efface bien toutes les références qui mènent à lui (en tant que père ou en tant que fils) mais ça n'efface pas ses fils (et encore moins ses petits-fils). On se retrouve donc avec des objets qui n'apparaissent plus comme fils de personne (même de NULL) dans la table des références, et il faut une boucle récurrente pour les éliminer couche par couche. Pas Glop, pas Glop !

Donc je dirai que l'autoréférence et le "INSTEAD OF DELETE" semble être la seule VRAIE solution pour contourner cette limitation de SQL par rapport à Access. J'essaye ça tout de suite !!

Cordialement,
Olivier

PS : Avant les triggers, je m'étais essayé aux "macros de tables Access" qui semblent en être un équivalent et je leur avais trouvé un gros inconvénient : Elles s'oublient facilement dans un coin ! A la fin, pour "comprendre" un SGBD, il faut aller voir les tables, les relations (avec leurs triggers implicites de type DELETE CASCADE), le code (VBA ou autre) et les "vrais" triggers (ou macros de tables) - soit quatre espaces conceptuels, avec chacun leur langage et leurs limites. Ça me rappelle un peu le développement Web avec l'HTML, le CSS, le PHP, le SQL et les effets de rebonds d'un processus conceptuellement unique mais qui se déploie successivement dans tous ces langages. D'ailleurs, j'ai entendu parler de SGBDs un peu antiques où relations et triggers étaient INTERDITS et ou TOUT était dans le code. J'approuve pas mais, au moins, ça centralise ...

[SQLpro]

"Access n'est pas un SGBD Relationnel" ?

Ce n'est pas parce que vous avez le langage SQL dans un SGBD qu'il est relationnel. Il est possible de faire du SQL sur un simple fichier texte, dont les zones sont délimitées. Et même sur une feuille Excel qui n'est pas du tout un SGBD, mais un tableur.

Les SGBDR relationnels obéissent à des règles mathématiques strictes associées à une logique fonctionnelle telle que définie par son créateur Franck Edgar Codd en 1970.
Vous trouverez le papier original des travaux de Codd à cette adresse :
https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

Outre l'algèbre relationnelle, le SGBD relationnel doit donc incorporer la gestion des transactions, ce qu'Acces ne sait pas faire. En gros, si vous mettez à jour des données dans votre base Access et qu'un crash survient, certaines données auront été modifiées, d'autres pas sans que vous puissiez savoir lesquelles ni revenir en arrière. La journalisation des transactions permet ces fonctionnalités (garantir la cohérence des transactions).

En 1985, harcelé par des vendeurs de SGBD non relationnel se prétendant être relationnel, Franck Edgar Codd précisa un certain nombre de points pour vérifier qu'un SGBD est bien relationnel. Ce sont les fameuses règles de Codd. Vous trouverez une étude sur le sujet ici :
https://sqlpro.developpez.com/SGBDR/ReglesCodd/
Force est de constater que :
Règle 4 : non appliquée dans Access
Règle 5 : non appliquée dans Access
Règle 6 : non appliquée dans Access
Règle 7 : non appliquée dans Access
Règle 10 : non appliquée dans Access
Règle 11 : non appliquée dans Access

On est donc TRÈS loin d'être relationnel !

En sus, Access ne permet pas d'ajouter de "l'intelligence" centralisée comme c'est le cas de tous les SGBD Relatrionnels :

• Procédure stockée
• Déclencheurs
• UDF
• ...

Ça fait trois ans que je travaille avec des accdb, et des accde, six applications avec de cinq à dix accès simultanés. Mes applis ont entre 30 et 80 tables, jusqu'à 230 requêtes explicitement nommées et entre 10.000 et 25.000 lignes de VBA. Alors ça ne me donne pas trop l'impression de "ne pas être quelque chose" ... ou je ne comprends ce "qu'être quelque chose" veut dire.

Maintenant, que SQL fasse des choses en plus, pourquoi pas ? J'espère juste que cette fameuse journalisation ne va pas écrouler les performances.

Rassurez vous, sauf à faire de grosses bêtises, SQL Server est actuellement le SGBDR le plus performant au monde sur la gestion des transactions...
http://www.tpc.org/tpce/results/tpce...ric_results=no

Ça serait amusant que des contraintes visant à améliorer les performances (avec 100 utilisateurs simultanés sur des tables de plus de 100.000 lignes ?) les dégrade quand il n'y a que trois utilisateurs travaillant sur des tables de 1000 lignes. Bon, je verrai vite ça à l'usage ... merci de l'avertissement !

L'optimisation des performances est gérer par l'optimiseur qui n'existe pas dans Access et dont la particularité est de calculer la meilleure façon de traiter votre demande, quitte à récrire complémentent la requête en s'aidant de :

• la sémantique (dont les contraintes font parties - PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY...)
• les statistiques (pour calculer un coût global estimé de la meilleure façon de traiter la requête, aboutissant au choix du plan d'exécution)

À ce jeu là, SQL server c'en sort le mieux devançant généralement tous ses concurrents (DB2, Oracle...) et très loin devant les gratuits comme PotGreSQL ou MySQL...

Maintenant, concernant mes DELETE CASCADE sur les tables auto-référencées, pas d'autre proposition que l'externalisation des références ? J'ai déjà commencé à coder ça, juste pour voir, mais si quelqu'un a une autre idée ...

Une solution vous a été fournie par déclencheur. Pour ma part je ne ferais jamais cela comme ça. J'utiliserait une procédure stockée... mais dans l'absolu je m'abstiendrait !

A +

[SQLpro]

...
Côté "performances de développement", les champs multivalués, ça dépote. Sur de très très grosses tables, ou pour faire du multivalué dynamique, je dis pas, mais, comme souvent, tout dépend du contexte.

Le problème est extrêmement simple. Pour les performances, il faut des index. Sauf que l'on ne peut pas indexer n'importe quoi. On ne peut indexer que ce qui est indexable. Par défaut toute données atomique (principe de base de l'algèbre relationnelle de Codd !), c'est à dire composée d'une seule information, est indexable.
Les données multivaluées violant le principe d'atomicité, ne sont, bien entendu, pas du tout indexable... Alors qu'en mettant toutes ces valeurs dans une colonnes d'une autre table, ces valeurs atomisées, redeviennent indexables. C'est donc encore pour des raisons de performances que SQL Server s'est interdit ce genre d'horreur !

A +

[escartefigue]

Bonsoir,



Bizarre, bizarre...

Point de vue tout à fait relatif et subjectif !

A l'inverse, DB2 for z/OS (depuis DB2 V2, 1988) a toujours autorisé (voire préconisé) ON DELETE CASCADE dans le cas des tables qui s’auto-référencent !
J'ai eu à pratiquer ça (à mon corps défendant) sur des très grosses tables, et pas de problème !

A l'inverse, j'ai connu il y a un peu moins longtemps (en DB2 V7 for Z/OS de mémoire), des problèmes de performances engendrés par des DELETE CASCADE vers des tables filles multiples.
DELETE CASCADE avantageusement remplacé dans ce cas par des DELETE SET NULL

[olra72]

Un grand merci à aieeeuuuuu pour son "INSTEAD OF DELETE" qui résout parfaitement mon problème !!

Pour la polémique sur ce qui est - ou n'est pas - un outil professionnel, relationnel, indexable ou patafiolé, j'ai tendance à adopter deux points de vue contradictoires :
- adopter des bases conceptuelles solides permettant (?) d'aller vers des outils puissants et fiables
- jeter la clarté aux orties et adopter une approche totalement opportuniste (si ça marche et que ça me permet de résoudre mon problème du moment, alors ...)

La première est ce que j'appellerai du Popperisme appliqué à l'informatique alors que la seconde se rapproche plus d'une approche anarchiste telle que défendue par Paul Feyerabend.

Pour ceux que ça intéresse, allez voir sur https://fr.wikipedia.org/wiki/Paul_Feyerabend et suivez le fil !

Encore merci à tous pour vos contributions et à bientôt pour d'autres aventures sur cet excellent forum !