使用基于条件的T-SQL添加约束 [英] Add a constraint using T-SQL based on a condition

问题描述

我试图添加一个基于条件的约束。
示例：

I am trying to add a constraint based on a condition. Example:

CREATE TABLE dbo.TestTable(
        [DbKey] [uniqueidentifier] NOT NULL,
        [GroupKey] [uniqueidentifier] NOT NULL,
        [EnableGroup] bit NOT NULL
        CONSTRAINT [TestTable_PK] PRIMARY KEY CLUSTERED 
        (
            [DbKey] ASC
        )
)ON [PRIMARY]

因此，可能有多个记录相同的GroupKey，但我想要至多一个记录，可以将EnableGroup设置为true为给定的GroupKey。

So, there could be multiple records that would have the same GroupKey, but I want that at most one record, could have the EnableGroup set to true for a given GroupKey.

任何帮助是赞赏。

推荐答案

您可以为表使用触发器：

You could use a trigger for your table:

if exists(select 1
            from inserted i
           where exists(select 1
                          from dbo.TestTable tt
                         where tt.GroupKey = i.GroupKey
                           and tt.EnableGroup = 1
                         group by tt.GroupKey
                        having count(*) > 1))
begin
   raiserror('Some meaningful error message here.')
   rollback
   return
end

这篇关于使用基于条件的T-SQL添加约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！