我有一个包含下表的 SQL Server 数据库:
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
以下存储过程返回所有规则及其关联类型的聚合值:
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
问题:
有没有办法强制 Value1、Value2、Value3 和 AssociatedTypes 组合的唯一性?
我需要防止创建与现有规则具有相同值(Value1、Value2 和 Value3)以及相同关联类型的新规则。同样的强制措施也应适用于更新现有规则。