cycles or multiple cascade paths

Version imprimable

Bonjour à tous,

je vous solicite, car j'ai un petit soucis.

Je vais préciser un peu ce que je veux faire ça sera plus simple je pense :mrgreen:
J'ai 3 tables :
- la table A
- la table B : elle fait référence à la clé primaire de A (ON DELETE CASCADE)
- la table C : elle fait référence à la clé primaire de A (ON DELETE CASCADE), de plus elle fait référence à la clé primaire de B (ON DELETE SET NULL)

La 2ème contrainte de C est importante, car on pourrait très bien supprimer un tuple de B sans avoir supprimé quoi que ce soit à A. Cependant en cas de suppression au niveau de A, C reçoit alors l'ordre de SET NULL et DELETE ... on ne sait ni lequel arrive en premier ni comment gérer l'un ou l'autre.

Au vue de la doc à ce sujet pas de surprise sauf que sémantiquement ce que je fais est juste : existe-t-il une façon de contourner ce problème ? en priorisant les contraintes les unes par rapport aux autres (ce que je ne sais évidemment pas faire :aie:) ?

J'obtiens donc l'erreur suivante (je travaille sur VS 2008 en C++ et j'utilise OLE DB, donc désolé si vous n'êtes pas habitués à ce genre de trace) :
Code:

1 2 3 4 OLE DB Error Record dump for hr = 0x80040e14 Row #: 0 Source: "Microsoft OLE DB Provider for SQL Server" Description: "Could not create constraint. See previous errors." Help File: "(null)" Help Context: 0 GUID: {0C733A63-2A1C-11CE-ADE5-00AA0044773D} Row #: 1 Source: "Microsoft OLE DB Provider for SQL Server" Description: "Introducing FOREIGN KEY constraint 'FK__note__idUtilisat__73501C2F' on table 'note' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." Help File: "(null)" Help Context: 0 GUID: {0C733A63-2A1C-11CE-ADE5-00AA0044773D} OLE DB Error Record dump end
(dans le trace le table 'note' est la table C dans mon explication)

Merci d'avance pour votre aide :zoubi:

29/07/2009, 08h58
SQLpro

Vous essayez de faire des contraintes circulaires. C'est un défaut général dans un modèle de données. Les références circulaires posent des problèmes et sont généralement le fait d'une modélisation incorrecte. Quel est votre MCD ?

A +
29/07/2009, 14h08
TheGzD

1 pièce(s) jointe(s)

Tout d'abord merci de passer du temps à essayer de m'aider :mrgreen:

Voici mon MCD (fait avec les moyens du bord) :

http://www.developpez.net/forums/att...1&d=1248869201

Il y a un cycle graphique oui, mais pas de circuit de part l'orientation des relations.

J'utilise d'ailleurs une bdd Mysql implémentant ce schéma et il n'y a absolument aucun problème.
Maintenant que je veux passer sur MS SQL Server c'est le bordel :cry:
Ils sont bien gentils chez MS, mais quand on pose une sécurité on s'assure qu'elle soit assez ciblée ou on permet de la désactiver (chose que je n'ai pas trouvé ...).

Je suis donc toujours coincé :pleure:

2 pièce(s) jointe(s)

Effectivement ce modèle n'a pas de cycle et pas de problème non plus de création de base. Je pense que vous avez du faire une erreur dans votre code SQL !

Voici les MCD, MPD et script SQL de création de la base tel que décrit et qui passent sans aucun problème !

MCD :
http://www.developpez.net/forums/att...1&d=1249248212

MPD :
http://www.developpez.net/forums/att...1&d=1249248221

Création de la base :

Code:

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/*==============================================================*/
/* Nom de SGBD :  Microsoft SQL Server 2005                     */
/* Date de création :  02/08/2009 23:18:34                      */
/*==============================================================*/
 
/*==============================================================*/
/* Table : CATEGORIE                                            */
/*==============================================================*/
create table CATEGORIE (
   CTG_ID               int                  identity,
   USR_ID               int                  not null,
   constraint PK_CATEGORIE primary key  (CTG_ID)
)
go
 
/*==============================================================*/
/* Index : POSSEDE_FK                                           */
/*==============================================================*/
create   index POSSEDE_FK on CATEGORIE (
USR_ID
)
go
 
/*==============================================================*/
/* Table : NOTE                                                 */
/*==============================================================*/
create table NOTE (
   NOT_ID               int                  identity,
   USR_ID               int                  not null,
   CTG_ID               int                  null,
   constraint PK_NOTE primary key  (NOT_ID)
)
go
 
/*==============================================================*/
/* Index : SPECIFIE_FK                                          */
/*==============================================================*/
create   index SPECIFIE_FK on NOTE (
CTG_ID
)
go
 
/*==============================================================*/
/* Index : REDIGE_FK                                            */
/*==============================================================*/
create   index REDIGE_FK on NOTE (
USR_ID
)
go
 
/*==============================================================*/
/* Table : UTILISATEUR                                          */
/*==============================================================*/
create table UTILISATEUR (
   USR_ID               int                  identity,
   constraint PK_UTILISATEUR primary key  (USR_ID)
)
go
 
alter table CATEGORIE
   add constraint FK_CATEGORI_POSSEDE_UTILISAT foreign key (USR_ID)
      references UTILISATEUR (USR_ID)
go
 
alter table NOTE
   add constraint FK_NOTE_REDIGE_UTILISAT foreign key (USR_ID)
      references UTILISATEUR (USR_ID)
go
 
alter table NOTE
   add constraint FK_NOTE_SPECIFIE_CATEGORI foreign key (CTG_ID)
      references CATEGORIE (CTG_ID)
go

A +

Bonsoir,

Le problème vient du fait que le SGBD considère qu’un stimulus partant de la table Utilisateur se propage par deux chemins différents (conterminous paths comme dirait Chris Date) jusqu’à la table Note, et même au plan théorique ça soulève des problèmes.

@ SQLpro

Vous avez utilisé NO ACTION pour chaque contrainte référentielle, donc le stimulus émis par la table Utilisateur parvient à la table Note seulement par un chemin (rien n’arrive du côté Catégorie), il n’y a donc pas de problème dans ce genre de configuration.

@ TheGzD

Il faudrait :

1) Remplacer l’option CASCADE entre Catégorie et Utilisateur par un NO ACTION, simplement pour faire plaisir au SGBD ;

2) Passer aux choses sérieuses en prévoyant un trigger de type INSTEAD OF DELETE, attaché à la table Utilisateur, où l’on commence par supprimer les catégories dépendant de l’utilisateur à supprimer, avant de supprimer celui-ci.

Quelque chose du genre :
Code:

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 create table Utilisateur ( uId char(4) not null , uNom varchar(32) not null , primary key (uId) ) create table Categorie ( cId char(4) not null , uId char(4) not null , cNom varchar(32) not null , primary key (cId) , foreign key (uId) references utilisateur on delete NO ACTION ) create table Note ( nId char(4) not null , uId char(4) not null , cId char(4) , nNom varchar(32) not null , primary key (nId) , foreign key (cId) references Categorie on delete set null , foreign key (uId) references utilisateur on delete CASCADE ) GO CREATE TRIGGER Utilisateur_DELETE ON Utilisateur Instead of DELETE AS DELETE FROM Categorie WHERE EXISTS (SELECT * FROM DELETED WHERE Categorie.uid = DELETED.uid) DELETE from Utilisateur WHERE EXISTS (SELECT * FROM DELETED WHERE Utilisateur.uid = DELETED.uid) ;
.

03/08/2009, 10h17
TheGzD

Merci à tous les 2 pour vous êtes penchés sur mon problème.

Citation:

Le problème vient du fait que le SGBD considère qu’un stimulus partant de la table Utilisateur se propage par deux chemins différents (conterminous paths comme dirait Chris Date) jusqu’à la table Note, et même au plan théorique ça soulève des problèmes.

merci d'avoir mis des termes exacts sur ce que j'avais essayé d'expliquer dans mon post initial :mrgreen: C'est pourquoi en cas de NO ACTION, sur au moins 1 des 3 contraintes, il n'y a pas de soucis.

Citation:

1) Remplacer l’option CASCADE entre Catégorie et Utilisateur par un NO ACTION, simplement pour faire plaisir au SGBD ;

2) Passer aux choses sérieuses en prévoyant un trigger de type INSTEAD OF DELETE, attaché à la table Utilisateur, où l’on commence par supprimer les catégories dépendant de l’utilisateur à supprimer, avant de supprimer celui-ci.

c'est ce à quoi j'étais arrivé, même si ça ne me convient qu'à moitier :? Un système de gestion des priorités des stimuli m'aurait déjà plus plu, mais si le SGBD ne le gère pas on ne va pas réinventer la roue non plus :roll:

Je vais mettre ceci en place ;)
Je vous tiens au courant en cas de soucis, merci encore.
03/08/2009, 12h38
SQLpro

En fait cette restriction de comportement vient du fait que SQL Server ne gére pas le déferrement des contraintes.
A lire sur le sujet : http://blog.developpez.com/sqlpro/p6...-deferabilite/

A +
03/08/2009, 14h01
TheGzD

et n'existerait-t-il pas un paramétrage du SGBD permettant de passer outre ? (c'est beau de rêver :roll:)
03/08/2009, 14h51
mikedavem

Lol oui c beau .. mais malheureusement non.

Comme l'a dit SQL Pro, SQL Server ne gère pas la déferrabilité des contraintes. Vous êtes donc obligé de passer par d'autres "artifices" comme vous l'a montré fsmrel

++
04/08/2009, 17h19
TheGzD

J'ai mis en place le trigger que m'a conseillé fsmrel et ça marche parfaitement.

Merci beaucoup :ccool: