delete d'un enregistrement dans une table avec une colonne autoréférencée [Résolu]

[lou87]

Bonjour,

J'ai une base de données qui comporte 3 tables.

La première table est nommée A, la seconde B et la troisième C.

Les tables B et C, récupère l'ID de la table A.

Et la table A a une colonne qui fait référence à son ID, nommée ID_PARENT.

Lors de la suppression d'une donnée de la table A, dont l'ID n'a aucune référence dans sa table au niveau de la colonne ID_PARENT, elle se supprime sans problème dans la table A mais aussi les données de la table B et C qui ont pour référence l'ID de la donnée supprimé dans la table A.

Cependant lorsque je veux supprimer une donnée dans la table A mais qui a une référence dans une autre donnée de la même table dans la colonne ID_PARENT, il me fait une erreur...

L'instruction DELETE est en conflit avec la contrainte SAME TABLE REFERENCE "FK__A__id_par__1B5E0D89". Le conflit s'est produit dans la base de données "test", table "dbo.A", column 'id_parent'.
L'instruction a été arrêtée.
Description : Une exception non gérée s'est produite au moment de l'exécution de la demande Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code.

Détails de l'exception: System.Data.SqlClient.SqlException: L'instruction DELETE est en conflit avec la contrainte SAME TABLE REFERENCE "FK__A__id_par__1B5E0D89". Le conflit s'est produit dans la base de données "test", table "dbo.A", column 'id_parent'.
L'instruction a été arrêtée.

Cette erreur est un peu logique quelque part mais j'aimerais pouvoir supprimer un enregistrement dans la table A même si elle est référencé dans un autre enregistrement, et supprimer aussi ce dernier du coup.

Est-ce assez explicite ?

Avez vous une idée sur mon problème ?

Ah encore une chose sur ma table B et C il était possible de rajouter la contrainte ON DELETE CASCADE, cependant je n'ai pas pu le faire sur la colonne d'auto-référencement de la table A.

En vous remerciant.

[lou87]

J'ai peut être commencé à trouver un début de réponse mais je ne suis pas sûr...

En faite j'exécute directement ma requête dans le deletecommand de mon gridview...

Et pour éviter toute erreur je pensais modifier s'il y a, les données ayant pour id_parent l'id de la donnée que l'on voudrais supprimer et le mettre à null...

J'ai déjà essayé ça pour le moment mais ça fonctionne pas...

Code :

1
2
deletecommand="Update PROPERTY SET id_parent = null WHERE (id_property = @id_property)
                   Delete from PROPERTY where id_property = @id_property">

Edit : Sinon est-il possible d'affecter à deletecommand du gridview qui se trouve dans ma page .aspx une fonction ? Si oui comment ?

En vous remerciant.

[witch]

Bonjour,

Tu n'as qu'à ajouter la contrainte sur tes table B et C ainsi ;

Code :

1
2
3
 
CONSTRAINT FK_id_parent FOREIGN KEY  (id_parent) REFERENCES A (id_parent) 
ON DELETE CASCADE

Voici la réponse sur ce thread :
http://www.developpez.net/forums/d45...de-sql-server/


++

[lou87]

Merci witch pour ta réponse mais je crois que je vais illustrer un peu mon problème pour que ce soit plus clair.

Exemple de ma table A :

Code :

1
2
3
4
id         libelle           id_parent
1             aaaa             
2             bbbb             
3             cccc                 2

Exemple de ma table B qui a la contrainte ON DELETE CASCADE sur son id_a :

Code :

1
2
3
id_b             id_a
1                     
2                 1

Exemple de ma table C qui a la contrainte ON DELETE CASCADE sur son id_a :

Code :

1
2
3
4
id_c            id_a
1                 1
2

Si je supprime mon premièr enregistrement dans ma table A, les enregistrements qui s'y rapportent dans la table B et C sont supprimés aussi.

Cependant si j'essaie de supprimer dans ma table A cette donnée (qui a pour id l'id_parent d'un autre enregistrement) ...

Code :

1
2
id         libelle           id_parent
2          bbbb

Je ne peux pas ce qui est logique quelque part à cause de la contrainte qui fait que la colonne id_parent est liés à la colonne id de la table A.

Est-ce un peu plus compréhensible ?

Le plus simple aurait été d'inclure dans la création de la table A une contrainte ON DELETE CASCADE sur id_parent de la table A, mais ce ne fut pas possible...

Voici le message que j'avais quand je voulais lui mettre cette contrainte :

Msg*1785, Niveau*16, État*0, Ligne*
L'introduction d'une contrainte FOREIGN KEY 'FK_id_parent' sur la table 'A' peut provoquer des cycles ou des accès en cascade multiples. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION, ou modifiez d'autres contraintes FOREIGN KEY.
Msg*1750, Niveau*16, État*0, Ligne*
Impossible de créer la contrainte. Voir les erreurs précédentes.

Donc maintenant ce que j'essaie de savoir si je ne peux pas modifier l'enregistrement dont l'id_parent fait référence à l'id de la donnée que je veux supprimer, avant de supprimer cette dernière ce qui éviterait de créer une erreur...

Et je voudrais appeler cette fonction dans ma page .aspx dans la commande deletecommand de mon gridview.

[CinePhil]

Une solution, pas forcément très élégante, serait d'autoriser id_parent à être NULL et mettre un ON DELETE SET NULL.

Une solution plus élégante consiste à modifier le modèle de données en externalisant les parents :
Parent (id_enfant, id_parent)
Ainsi, plus de problème pour mettre ON DELETE CASCADE sur cette table. Et avec une vue sur les deux tables, tu reconstitues l'actuelle.

Par contre, s'il y a des petits enfants, ça peut poser problème ! Il faudrait alors mettre en oeuvre un trigger. D'ailleurs, à la réflexion vite fait, je crois qu'un trigger est de toute façon nécessaire car dans la table principale l'enfant ne sera pas supprimé.

[aieeeuuuuu]

Envoyé par lou87

J'ai déjà essayé ça pour le moment mais ça fonctionne pas...

Code :

qu'est-ce qui ne fonctionne pas ? quel est le message d'erreur ?

ta table s'appelle property ??? loin d'être judicieux comme nom de table ! c'est un mot clef...

d'autre part, ça n'est pas id_property qui doit être mis à null mais id_parent

essaie donc comme ca :
Code sql :

UPDATE PROPERTY SET id_parent = NULL WHERE id_parent = @id_property
DELETE FROM PROPERTY WHERE id_property = @id_property



(si j'ai bien compris, tu souhaites quand même conserver tes lignes enfant, simplement les détacher de leur parent ?)

heu... tu crains pas d'avoir la DDASS sur le dos ? ok je sors ...

[lou87]

Bonjour !

Merci CinePhil et aieeeuuuuu pour vos réponses !

Grâce à la dernière solution ça fonctionne très bien !

Merci encore !

Bonne journée.