Aplicar exclusividade na combinação de colunas + função agregada

Infelizmente, a resposta do @SQLpro não vai funcionar porque STRING_AGGnão é suportada em visualizações indexadas.

Em vez disso, você pode usar um gatilho para impor isso, armazenando o agregado TypeIdnovamente Rulee adicionando uma UNIQUErestrição a ele.

ALTER TABLE dbo.[Rule]
ADD AllTypeIds varchar(1600) NULL;

Insira seus dados agregados e adicione uma restrição exclusiva

WITH Aggregated AS (
    SELECT
      rt.RuleId, 
      STRING_AGG(CAST(rt.TypeId AS varchar(12)), '|')
        WITHIN GROUP (ORDER BY rt.TypeId) AS AllTypeIds
    FROM dbo.RuleType rt ON rt.RuleId = cr.RuleId
    GROUP BY
      rt.RuleId
)
UPDATE r
SET AllTypeIds = a.AllTypeIds
FROM dbo.[Rule] r
JOIN Aggregated a ON a.RuleId = r.RuleId

ALTER TABLE dbo.[Rule]
ADD CONSTRAINT RuleMustBeUniqueValuesAndTypes UNIQUE (Value1, Value2, Value3, AllTypeIds);

E a lógica do gatilho é a seguinte:

• Pegue as tabelas insertede deletede junte-as para obter alterações exclusivasRuleId
• Junte-se à esquerda paraRuleType
• Agregue RuleIde obtenha todos TypeIdos valores.
• Junte-se novamente Rulee atualize a coluna.
• Se houver um conjunto duplicado, a restrição exclusiva gerará um erro.
• Observe que também precisamos levar em consideração atualizações e exclusões, pois você pode remover linhas de um conjunto e fazê-lo colidir com outro conjunto.

CREATE OR ALTER TRIGGER RuleType_UniqueRule
ON dbo.RuleType
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;

SET NOCOUNT ON;

WITH ChangedRule AS (
    SELECT i.RuleId
    FROM inserted i
  
    UNION
  
    SELECT d.RuleId
    FROM deleted d
),
Aggregated AS (
    SELECT
      cr.RuleId, 
      STRING_AGG(CAST(rt.TypeId AS varchar(12)), '|')
        WITHIN GROUP (ORDER BY rt.TypeId) AS AllTypeIds
    FROM ChangedRule cr
    LEFT JOIN dbo.RuleType rt ON rt.RuleId = cr.RuleId
    GROUP BY
      cr.RuleId
)
UPDATE r
SET AllTypeIds = a.AllTypeIds
FROM dbo.[Rule] r WITH (SERIALIZABLE)
JOIN Aggregated a ON a.RuleId = r.RuleId
WHERE r.AllTypeIds IS DISTINCT FROM a.AllTypeIds;

banco de dados<> violino

Notas adicionais:

• O tamanho máximo para uma restrição exclusiva é 1.700 bytes, portanto limitamos a coluna a varchar(1600). Uma agregação maior falhará com um erro de truncamento.
• Se você precisar que seja mais amplo do que isso, poderá ganhar um pouco usando varbinaryalgum tipo de algoritmo de compactação de bits. Qualquer coisa muito maior precisará de uma solução completamente diferente.
• NULLos valores também são considerados únicos, portanto você não poderia ter vários Rules sem nenhum TypeId . Se isso não for desejado, altere sua restrição para um índice exclusivo filtrado.

  CREATE UNIQUE NONCLUSTERED INDEX RuleMustBeUniqueValuesAndTypes ON dbo.[Rule]
  (Value1, Value2, Value3, AllTypeIds)
  WHERE (AllTypeIds IS NOT NULL);
  

SQLpro sugere uma boa resposta, mas a junção/agregação externa não permite isso, até onde eu sei.

Como alternativa, você pode:

1. Crie uma restrição exclusiva na regra nos valores 1,2,3 e ID da regra.

2. Crie uma restrição exclusiva em RuleType para que nenhum tipo de regra possa ter a mesma definição.

Acho que isso acontecerá desde que 2) seja tolerável.

Como a exclusividade não pode ser única quando os valores são NULL, você pode criar uma visualização indexada com um índice UNIQUE CLUSTERED como:

CREATE VIEW V_RULES
WITH SCHEMABINDING
AS
SELECT R.RuleId, 
       R.Value1, 
       R.Value2, 
       R.Value3,
       STRING_AGG(RT.TypeId, ', ') AS AssoctiatedTypes 
FROM   dbo.Rule R
       INNER JOIN dbo.RuleType RT 
          ON RT.RuleId = R.RuleId
GROUP  BY R.RuleId, 
       R.Value1, 
       R.Value2, 
       R.Value3;
GO

CREATE UNIQUE CLUSTERED INDEX X_V 
   ON V_RULES (R.Value1, R.Value2, R.Value3, AssoctiatedTypes);
GO