Discussion :

[TheGzD]

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
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 ) ?

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 :

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

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

[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 +

[TheGzD]

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

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



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
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é

[SQLpro]

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 :

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
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 +

[fsmrel]

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 SQL :

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
 
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) ;

.

[TheGzD]

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

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 C'est pourquoi en cas de NO ACTION, sur au moins 1 des 3 contraintes, il n'y a pas de soucis.

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

Je vais mettre ceci en place
Je vous tiens au courant en cas de soucis, merci encore.

[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 +

[TheGzD]

et n'existerait-t-il pas un paramétrage du SGBD permettant de passer outre ? (c'est beau de rêver )

[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

++

[TheGzD]

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

Merci beaucoup