1

I have a SQL Server database with the following tables:

enter image description here

    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 

enter image description here

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.

3 Answers 3

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);
    
1
  • Thank you, Charlieface! Your solution worked beautifully. Mar 14 at 11:07
0

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.

-2

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
4
  • Does this work? I can't create an index on a view with string_agg Mar 13 at 13:57
  • You are right, I am wrong.... STRING_AGG won't work with indexed views...
    – SQLpro
    Mar 13 at 17:55
  • Even if it did you would need a deterministic ordering for STRING_AGG, as an example '1,2' <> '2,1' Mar 14 at 20:22
  • This is possible in SQL Server 2022 with the WITHIN GROUP clause
    – SQLpro
    Mar 15 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.