Empêcher la référence circulaire dans la table MS-SQL

Il semble que vous ayez défini des règles métier pour votre table:

• Toute chaîne doit se terminer par un compte de premier niveau
• Une chaîne peut ne pas avoir de référence circulaire

Vous pouvez appliquer cela de deux manières.

Vous pouvez créer un trigger dans votre base de données, et vérifiez la logique du trigger. Cela a l'avantage de s'exécuter à l'intérieur de la base de données, de sorte qu'il s'applique à chaque transaction, quel que soit le client. Cependant, les déclencheurs de base de données ne sont pas toujours populaires. Je les vois comme un effet secondaire , et ils peuvent être difficiles à déboguer. Les déclencheurs s'exécutent dans le cadre de votre SQL, donc s'ils sont lents, votre SQL sera lent.

L'alternative consiste à appliquer cette logique dans la couche application - tout ce qui concerne votre base de données. C'est plus facile à déboguer et rend votre logique métier explicite aux nouveaux développeurs - mais elle ne s'exécute pas à l'intérieur de la base de données, donc vous pourriez finir par répliquer la logique si vous avez plusieurs applications clientes.

La gestion des tables auto-référencées / relations récursives en SQL n'est pas simple. Je suppose que cela est démontré par le fait que plusieurs personnes ne peuvent pas comprendre le problème en vérifiant simplement les cycles à une seule profondeur.

Pour appliquer cela avec des contraintes de table, vous auriez besoin d'une contrainte de vérification basée sur une requête récursive. Au mieux, c'est un support spécifique au SGBD, et il peut ne pas fonctionner correctement s'il doit être exécuté à chaque mise à jour.

Mon conseil est que le code contenant l'instruction UPDATE applique cela. Cela pourrait prendre plusieurs formes. Dans tous les cas, si cela doit être strictement appliqué, cela peut nécessiter de limiter l'accès UPDATE dans la table à un compte de service utilisé par un proc stocké ou un service externe.

L'utilisation d'une procédure stockée serait similaire à une contrainte CHECK, sauf que vous pouvez utiliser une logique procédurale (itérative) pour rechercher des cycles avant d'effectuer la mise à jour. Cependant, il est devenu impopulaire de mettre trop de logique dans les processus stockés, et la question de savoir si ce type de vérification doit être fait est un jugement d'équipe à équipe / d'organisation à organisation.

De même, l'utilisation d'une approche basée sur les services vous permettrait d'utiliser la logique procédurale pour rechercher des cycles, et vous pourriez l'écrire dans un langage mieux adapté à une telle logique. Le problème ici est que si les services ne font pas partie de votre architecture, il est un peu difficile d'introduire une toute nouvelle couche. Mais, une couche de service est probablement considérée comme plus moderne / populaire (du moins pour le moment) que la canalisation des mises à jour via des processus stockés.

En gardant ces approches à l'esprit - et en comprenant que la syntaxe procédurale et récursive dans les bases de données est spécifique au SGBD - il y a trop d'options de syntaxe possibles pour vraiment entrer. Mais l'idée est:

• Examinez le parent proposé.
• Vérifiez son parent de manière récursive
• Avez-vous déjà atteint l'enfant proposé avant d'accéder à un compte de niveau supérieur? Sinon, autorisez la mise à jour

Voici un exemple que vous pouvez utiliser comme base pour implémenter une contrainte de base de données qui devrait empêcher les références circulaires dans les mises à jour d'une seule ligne; Je ne pense pas que cela empêchera une référence circulaire si plusieurs lignes sont mises à jour.

/*
ALTER TABLE dbo.Test  DROP CONSTRAINT chkTest_PreventCircularRef
GO
DROP FUNCTION dbo.Test_PreventCircularRef 
GO
DROP TABLE dbo.Test 
GO
*/

CREATE TABLE dbo.Test (TestID INT PRIMARY KEY,TestID_Parent INT)
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 1 AS TestID,NULL  AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 2 AS TestID,1     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 3 AS TestID,2     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 4 AS TestID,3     AS TestID_Parent
INSERT INTO dbo.Test(TestID,TestID_Parent) SELECT 5 AS TestID,4     AS TestID_Parent
GO

GO
CREATE FUNCTION dbo.Test_PreventCircularRef (@TestID INT,@TestID_Parent INT)
RETURNS INT
BEGIN
    --FOR TESTING:
    --SELECT * FROM dbo.Test;DECLARE @TestID INT=3,@TestID_Parent INT=4

    DECLARE @ParentID INT=@TestID
    DECLARE @ChildID INT=NULL
    DECLARE @RetVal INT=0
    DECLARE @Ancestors TABLE(TestID INT)
    DECLARE @Descendants TABLE(TestID INT)

    --Get all descendants
    INSERT INTO @Descendants(TestID) SELECT TestID FROM dbo.Test WHERE TestID_Parent=@TestID
    WHILE (@@ROWCOUNT>0)
    BEGIN
        INSERT INTO @Descendants(TestID)
            SELECT t1.TestID
            FROM dbo.Test t1
            LEFT JOIN @Descendants relID ON relID.TestID=t1.TestID
            WHERE relID.TestID IS NULL
            AND t1.TestID_Parent IN (SELECT TestID FROM @Descendants)
    END

    --Get all ancestors
    --INSERT INTO @Ancestors(TestID) SELECT TestID_Parent FROM dbo.Test WHERE TestID=@TestID
    --WHILE (@@ROWCOUNT>0)
    --BEGIN
    --  INSERT INTO @Ancestors(TestID)
    --      SELECT t1.TestID_Parent
    --      FROM dbo.Test t1
    --      LEFT JOIN @Ancestors relID ON relID.TestID=t1.TestID_Parent
    --      WHERE relID.TestID IS NULL
    --      AND t1.TestID_Parent IS NOT NULL
    --      AND t1.TestID IN (SELECT TestID FROM @Ancestors)
    --END

    --FOR TESTING:
    --SELECT TestID AS [Ancestors] FROM @Ancestors;SELECT TestID AS [Descendants] FROM @Descendants;

    IF EXISTS (
        SELECT *
        FROM @Descendants
        WHERE TestID=@TestID_Parent
    )
    BEGIN
        SET @RetVal=1
    END

    RETURN @RetVal
END
GO

ALTER TABLE dbo.Test 
  ADD CONSTRAINT chkTest_PreventCircularRef
  CHECK (dbo.Test_PreventCircularRef(TestID,TestID_Parent) = 0); 
GO

SELECT * FROM dbo.Test

--This is problematic as it creates a circular reference between TestID 3 and 4; it is now prevented
UPDATE dbo.Test SET TestID_Parent=4 WHERE TestID=3

Enfin, j'ai créé les scripts après quelques échecs, cela fonctionne très bien pour moi.

   -- To hold the Account table data
   Declare @Accounts table (ID INT, ParentAccountID INT) 

   -- To be updated 
   Declare @AccountID       int = 4;
   Declare @ParentAccountID int = 7;

   Declare @NextParentAccountID INT = @ParentAccountID

   Declare @IsCircular int = 0

   INSERT INTO @Accounts values (1, NULL), (2,1), (3,1) ,(4,3), (5,4), (6,5), (7,6), (8,7)

   -- No circular reference value
   --Select * from @Accounts

   -- Request to update ParentAccountID to 7 for the Account ID 4
   update @Accounts  
   set ParentAccountID = @ParentAccountID
   where ID = @AccountID

   Select * from @Accounts

   WHILE(1=1)
   BEGIN            
       -- Take the ParentAccountID for @NextParentAccountID
       SELECT @NextParentAccountID = ParentAccountID from @Accounts WHERE ID = @NextParentAccountID  

       -- If the @NextParentAccountID is NULL, then it reaches the top level account, no circular reference hence break the loop 
       IF (@NextParentAccountID IS NULL) 
       BEGIN
        BREAK;
       END

       -- If the @NextParentAccountID is equal to @AccountID (to which the update was done) then its creating circular reference
       -- Then set the @IsCircular to 1 and break the loop
       IF (@NextParentAccountID = @AccountID ) 
       BEGIN
        SET @IsCircular = 1
        BREAK
       END
   END

IF @IsCircular = 1 
    BEGIN 
        select 'CircularReference' as 'ResponseCode'
    END