我有一个包含下表的 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)以及相同关联类型的新规则。同样的强制措施也应适用于更新现有规则。
不幸的是,@SQLpro 的答案不起作用,因为
STRING_AGG
索引视图不支持。您可以使用触发器来强制执行此操作,方法是将聚合存储
TypeId
回其中Rule
并UNIQUE
在其上添加约束。插入聚合数据,并添加唯一约束
触发逻辑如下:
inserted
和deleted
表合并在一起以获得独特的变化RuleId
RuleType
RuleId
并获取所有TypeId
值。Rule
并更新专栏。数据库<>小提琴
进一步说明:
varchar(1600)
. 较大的聚合将因截断错误而失败。varbinary
某种位打包算法来获得一些收益。任何更大的东西都需要完全不同的解决方案。NULL
值也被认为是唯一的,因此您不能有多个Rule
s 而根本没有。TypeId
如果不需要这样做,请将约束更改为已过滤的唯一索引。SQLpro 建议了一个很好的答案,但据我所知,外连接/聚合不允许这样做。
作为替代方案,您可以:
对值 1、2、3 和规则 ID 的规则创建唯一约束。
在 RuleType 上创建唯一约束,以便任何规则类型都不能具有相同的定义。
我认为只要 2) 可以忍受,就可以了。
由于当值为 NULL 时唯一性不能是唯一的,因此您可以使用 UNIQUE CLUSTERED 索引创建索引视图,例如: