1
votes

Suppression de nœuds non connectés de la base de données SQL

Je travaille sur une arborescence avec une hiérarchie d'entreprise. Toutes les données sont stockées dans une base de données SQL Server.

La base de données comporte deux colonnes avec ID et ID de nœud parent (les deux sont varchar (5) ).

Désormais, lorsque je supprime un nœud, tous les enfants du nœud (et leurs enfants) seront laissés dans la base de données - non connectés. Comment suis-je censé les supprimer?

EDIT: J'utilise https://www.codeproject.com / Articles / 18378 / Organization-Chart-Generator pour générer le graphique.

c# sql-server tree

5 commentaires

Je comprends que vous essayez de stocker une structure arborescente dans un DB à une table. Une meilleure approche pourrait être d'avoir plus de tables avec des clés étrangères correspondantes entre les nœuds parents et enfants. Quelle est la structure arborescente exacte? Est-ce un arbre B? quelle est la hauteur de votre arbre?

il semble que vous n'avez pas de configuration de clés étrangères dans cette table, et par conséquent vous pouvez corrompre la table en supprimant les parents. Je commencerais par corriger la conception et configurer les clés étrangères appropriées.

Vous allez devoir créer une requête récursive (en utilisant un CTE) qui obtiendra tous ses enfants, puis supprimera d'abord du nœud le plus profond, supprimant enfin le nœud final.

@GuidoG J'ai oublié de mentionner que j'ai aussi une autre colonne d'identifiant PRIMARY IDENTITY (1, 1) - je pensais juste que ce n'était pas pertinent.

@apomene Je ne sais pas exactement quelle est la structure exacte. Les nœuds parents peuvent avoir un nombre illimité d'enfants et je ne stocke que des données sur les parents des nœuds. De plus, la hauteur maximale de l'arbre est de 4.

3 Réponses :

2
votes

S'il n'y a pas de clé étrangère, cela le fera. Je crée un exemple de table appelé nœuds avec deux colonnes, ID et Parent. Ce sont des entiers dans ma version mais cela n'a pas d'importance.

declare @temp table(id int, depth int)

declare @target int; set @target=2
;with cte as 
(
select *, 1 as depth from nodes where id=@target
union all
select nodes.*, depth+1 from nodes
join cte on cte.id=nodes.parent
)
insert @temp 
select id,depth from cte

while exists(select * from @temp)
begin
    delete nodes from nodes
    join @temp t on t.id=nodes.id
    where depth=(select max(depth) from @temp)

    delete @temp where depth=(select max(depth) from @temp)
end

Le résultat est:

select * from nodes

1   NULL
3   1
5   NULL

Créez un CTE qui traverse les relations

declare @target int; set @target=2
;with cte as 
(
select *, 1 as depth from nodes where id=@target
union all
select nodes.*, depth+1 from nodes
join cte on cte.id=nodes.parent
)
delete nodes where id in (select id from cte)

Voici le résultat

id  parent
1   NULL
2   1
3   1
4   2
5   NULL

Si vous avez des clés étrangères, vous devrez examiner cela de la plus grande profondeur au plus bas pour éviter les erreurs. Cela le fera (sans curseur)

create table nodes(id int, parent int)
insert nodes values (1,null),(2,1),(3,1),(4,2),(5,null)

select * from nodes

Le résultat est le même.

0 commentaires

2
votes

Vous pouvez le faire en utilisant une procédure récursive: en supprimant récursivement d'abord tous les fils d'un nœud, puis en supprimant ce nœud.

CREATE PROCEDURE DELETE_NODE
  @NODE_ID int
AS
BEGIN
  declare @CHILD_NODE_ID int;    
  declare CHILDS cursor for select NODE_ID from MY_TABLE where PARENT_NODE_ID = @NODE_ID;

  open CHILDS;
  fetch next from CHILDS into @CHILD_NODE_ID;
  while @@fetch_status = 0 
  begin  
    exec DELETE_NODE @NODE_ID = @CHILD_NODE_ID;

    fetch next from CHILDS into @CHILD_NODE_ID;
  end
  close CHILDS;
  deallocate CHILDS;

  delete from MY_TABLE where NODE_ID = @NODE_ID
END
GO

En passant, vous devez ajouter une relation (étrangère key) sur votre table avec elle-même pour les champs PARENT_NODE_ID et NODE_ID, vous ne pouvez donc pas supprimer de nœuds laissant les enfants non connectés.

0 commentaires

3
votes

Vous avez besoin de 2 choses ici,

Il vous faut d'abord un système qui empêche de supprimer un maître qui a enfants.
Deuxièmement, vous avez besoin d'une procédure pour supprimer un maître avec tous ses fils.

Le premier est nécessaire pour que personne ne puisse supprimer un maître en utilisant un autre moyen que la procédure correcte.

Voici un exemple de la façon dont vous pouvez configurer un tel système

create table test (
  id int not null identity,
  name varchar(10),
  parentid int  null,

  constraint pk_id primary key (id),  
  constraint fk_pid foreign key (parentid) references test (id) 
)

insert into test (name, parentid) 
values ('master', null), ('child1', 1), ('child2', 1), ('child3', 3)

maintenant lorsque vous supprimez par exemple la première ligne (name = master) alors le serveur sql vous arrêtera, et retournera une erreur disant que la clé étrangère fk_pid est violé.
En d'autres termes, personne ne peut plus supprimer un maître tant qu'il a encore des enfants.

Maintenant, pour une procédure qui peut supprimer un maître avec tous ses enfants, vous pouvez regarder les autres réponses et choisir simplement celle que vous préférez.

0 commentaires