Tabelas com hierarquia: crie uma restrição para evitar a circularidade por meio de chaves estrangeiras

Eu vi 2 maneiras principais de impor isso:

1, o jeito ANTIGO:

CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FooHierarchy VARCHAR(256),
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

A coluna FooHierarchy conteria um valor como este:

"|1|27|425"

Onde os números são mapeados para a coluna FooId. Você então imporia que a coluna Hierarchy terminasse com "|id" e o restante da string correspondesse a FooHieratchy do PARENT.

2, a NOVA maneira:

O SQL Server 2008 tem um novo tipo de dados chamado HierarchyID , que faz tudo isso para você.

Ele opera da mesma maneira que o modo ANTIGO, mas é tratado com eficiência pelo SQL Server e é adequado para uso como SUBSTITUIÇÃO para sua coluna "ParentID".

CREATE TABLE Foo 
    (FooId BIGINT PRIMARY KEY,
     FooHierarchy HIERARCHYID )

É meio que possível: você pode invocar uma UDF escalar de sua restrição CHECK e pode detectar ciclos de qualquer tamanho. Infelizmente, essa abordagem é extremamente lenta e pouco confiável: você pode ter falsos positivos e falsos negativos.

Em vez disso, eu usaria o caminho materializado.

Outra forma de evitar ciclos é ter um CHECK(ID > ParentID), o que provavelmente também não é muito viável.

Outra maneira de evitar ciclos é adicionar mais duas colunas, LevelInHierarchy e ParentLevelInHierarchy, ter (ParentID, ParentLevelInHierarchy) referir-se a (ID, LevelInHierarchy) e ter um CHECK(LevelInHierarchy > ParentLevelInHierarchy).

Você está usando o modelo de Lista de Adjacência , onde é difícil aplicar tal restrição.

Você pode examinar o modelo Nested Set , onde apenas hierarquias verdadeiras podem ser representadas (sem caminhos circulares). Isso tem outras desvantagens, como inserções/atualizações lentas.

Acredito que seja possível:

create function test_foo (@id bigint) returns bit
as
begin
declare @retval bit;

with t1 as (select @id as FooId, 0 as lvl  
union all 
 select f.FooId , t1.lvl+1 from t1 
 inner join Foo f ON (f.ParentFooId = t1.FooId)
 where lvl<11) -- you said that max nested level 10, so if there is any circular   
-- dependency, we don't need to go deeper than 11 levels to detect it

 select @retval =
 CASE(COUNT(*)) 
 WHEN 0 THEN 0 -- for records that don't have children
 WHEN 1 THEN 0 -- if a record has children
  ELSE 1 -- recursion detected
 END
 from t1
 where t1.FooId = @id ;

return @retval; 
end;
GO
alter table Foo add constraint CHK_REC1 CHECK (dbo.test_foo(ParentFooId) = 0)

Posso ter perdido alguma coisa (desculpe, não posso testar completamente), mas parece funcionar.

Aqui está outra opção: um gatilho que permite atualizações de várias linhas e não impõe ciclos. Ele funciona percorrendo a cadeia ancestral até encontrar um elemento raiz (com pai NULL), provando assim que não há ciclo. É limitado a 10 gerações, pois é claro que um ciclo é infinito.

Funciona apenas com o conjunto atual de linhas modificadas, portanto, desde que as atualizações não toquem em um grande número de itens muito profundos na tabela, o desempenho não deve ser tão ruim. Ele tem que percorrer todo o caminho da cadeia para cada elemento, portanto, terá algum impacto no desempenho.

Um gatilho verdadeiramente "inteligente" procuraria ciclos diretamente, verificando se um item alcançou a si mesmo e, em seguida, saltou. No entanto, isso requer a verificação do estado de todos os nós encontrados anteriormente durante cada loop e, portanto, requer um loop WHILE e mais codificação do que eu gostaria de fazer agora. Isso não deve ser realmente mais caro porque a operação normal seria não ter ciclos e, neste caso, será mais rápido trabalhar apenas com a geração anterior em vez de todos os nós anteriores durante cada loop.

Eu adoraria receber informações de @AlexKuznetsov ou de qualquer outra pessoa sobre como isso funcionaria no isolamento de instantâneo. Eu suspeito que não ficaria muito bem, mas gostaria de entender melhor.

CREATE TRIGGER TR_Foo_PreventCycles_IU ON Foo FOR INSERT, UPDATE
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

IF EXISTS (
   SELECT *
   FROM sys.dm_exec_session
   WHERE session_id = @@SPID
   AND transaction_isolation_level = 5
)
BEGIN;
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END;
DECLARE
   @CycledFooId bigint,
   @Message varchar(8000);

WITH Cycles AS (
   SELECT
      FooId SourceFooId,
      ParentFooId AncestorFooId,
      1 Generation
   FROM Inserted
   UNION ALL
   SELECT
      C.SourceFooId,
      F.ParentFooId,
      C.Generation + 1
   FROM
      Cycles C
      INNER JOIN dbo.Foo F
         ON C.AncestorFooId = F.FooId
   WHERE
      C.Generation <= 10
)
SELECT TOP 1 @CycledFooId = SourceFooId
FROM Cycles C
GROUP BY SourceFooId
HAVING Count(*) = Count(AncestorFooId); -- Doesn't have a NULL AncestorFooId in any row

IF @@RowCount > 0 BEGIN
   SET @Message = CASE WHEN EXISTS (SELECT * FROM Deleted) THEN 'UPDATE' ELSE 'INSERT' END + ' statement violated TRIGGER ''TR_Foo_PreventCycles_IU'' on table "dbo.Foo". A Foo cannot be its own ancestor. Example value is FooId ' + QuoteName(@CycledFooId, '"') + ' with ParentFooId ' + Quotename((SELECT ParentFooId FROM Inserted WHERE FooID = @CycledFooId), '"');
   RAISERROR(@Message, 16, 1);
   ROLLBACK TRAN;   
END;

Atualizar

Eu descobri como evitar uma junção extra de volta à tabela Inserted. Se alguém vir uma maneira melhor de fazer o GROUP BY para detectar aqueles que não contêm um NULL, por favor me avise.

Também adicionei uma opção para READ COMMITTED se a sessão atual estiver no nível SNAPSHOT ISOLATION. Isso evitará inconsistências, embora, infelizmente, cause maior bloqueio. Isso é inevitável para a tarefa em questão.

Se seus registros estiverem aninhados em mais de 1 nível, uma restrição não funcionará (presumo que você queira dizer, por exemplo, que o registro 1 é o pai do registro 2 e o registro 3 é o pai do registro 1). A única maneira de fazer isso seria no código pai ou com um gatilho, mas se você estiver olhando para uma tabela grande e vários níveis, isso seria bastante intensivo.