Enforce uniqueness on combination of columns + aggregate function

Question

I have a SQL Server database with the following tables:

    CREATE TABLE [dbo].[Rule]
    (
        [RuleId] [int] IDENTITY(1,1) NOT NULL,
        [Value1] [int] NOT NULL,
        [Value2] [int] NOT NULL,
        [Value3] [int] NOT NULL,
     CONSTRAINT [PK_Rule] PRIMARY KEY CLUSTERED ([RuleId] ASC)
     )
     GO
    
    CREATE TABLE [dbo].[Type]
    (
        [TypeId] [INT] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([TypeId] ASC)
    )
    GO
    
     CREATE TABLE [dbo].[RuleType]
     (
        [RuleId] [int] NOT NULL,
        [TypeId] [int] NOT NULL,
     CONSTRAINT [PK_RuleType] PRIMARY KEY CLUSTERED ([RuleId] ASC,  [TypeId] ASC)
    )
    GO
    
    ALTER TABLE [dbo].[RuleType]  WITH CHECK ADD  CONSTRAINT [FK_RuleType_Type] FOREIGN KEY([TypeId])
    REFERENCES [dbo].[Type] ([TypeId])
    GO
    
    ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Type]
    GO
    
    ALTER TABLE [dbo].[RuleType]  WITH CHECK ADD  CONSTRAINT [FK_RuleType_Rule] FOREIGN KEY([RuleId])
    REFERENCES [dbo].[Rule] ([RuleId])
    GO
    
    ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Rule]
    GO

/*Test data*/
INSERT INTO [dbo].[Type] ([Description]) VALUES ('Type1'), ('Type2'), ('Type3');
GO

INSERT INTO [dbo].[Rule] ([Value1], [Value2], [Value3]) VALUES (1,1,1), (2,2,2), (3,3,3);
GO

INSERT INTO [dbo].[RuleType] ([RuleId], [TypeId]) VALUES (1,1), (1,2), (1,3), (2,2), (2,3);
GO

The following stored procedure returns all the rules and an aggregate value of their associated types:

CREATE OR ALTER PROCEDURE SelectRules
AS

    SELECT 
        R.[RuleId], 
        R.[Value1], 
        R.[Value2], 
        R.[Value3],
        [AssoctiatedTypes]  = STRING_AGG(RT.TypeId, ', ')
    FROM
        [dbo].[Rule] R
    LEFT JOIN
        [dbo].[RuleType] RT ON RT.RuleId = R.RuleId
    GROUP BY
        R.[RuleId], 
        R.[Value1], 
        R.[Value2], 
        R.[Value3]
GO 

Question:

Is there a way to enforce uniqueness on the combination Value1, Value2, Value3 and AssociatedTypes?

What I need to prevent is the creation of a new rule with the same values (Value1, Value2 and Value3) and the same associated types as an existing rule. The same enforcement should apply for updating an existing rule.

Answer 1

Unfortunately, @SQLpro's answer isn't going to work, because STRING_AGG is not supported in Indexed Views.

You can instead use a trigger to enforce this, by storing the aggregated TypeId back into Rule and adding a UNIQUE constraint on it.

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

Insert your aggregated data, and add a unique constraint

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);

And the trigger logic is as follows:

• Take the inserted and deleted tables and union them together to get unique changed RuleId
• Left-join that to RuleType
• Aggregate by RuleId and get all TypeId values.
• Join back to Rule and update the column.
• If there is a duplicate set then the unique constraint will throw an error.
• Note that we need to take into account updates and deletes as well, because you could remove rows from a set and make it clash with another set.

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;

db<>fiddle

Further notes:

• The maximum size for a unique constraint is 1700 bytes, so we limit the column to varchar(1600). A larger aggregation will fail with a truncation error.
• If you need it to be wider than that then you could gain a bit by using varbinary and some kind of bit-packing algorithm. Anything much larger will need a completely different solution.
• NULL values are also considered unique, so you couldn't have multiple Rules with no TypeId at all. If this is not desired then change your constraint to a filtered unique index.

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

Answer 2

SQLpro suggests a nice answer but the outer join/aggregate does not allow it as far as I can tell.

As an alternative, you can:

1. Create a unique constraint on Rule on values 1,2,3 and Rule ID.

2. Create a unique constraint on RuleType so no rule types can have the same definition.

I think that will do it so long as 2) is tolerable.

Answer 3

As uniqueness can't be unique when values are NULL, you can create an indexed view with a UNIQUE CLUSTERED index like :

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