Add a constraint using T-SQL based on a condition

Question

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]

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.

Any help is appreciated.

possible duplicate of [T-SQL Unique Constraint WHERE AnotherColumn = ParticularValue](http://stackoverflow.com/questions/4097484/t-sql-unique-constraint-where-anothercolumn-particularvalue) — Damien_The_Unbeliever, Feb 25 '11 at 11:36
Just realised that the question I linked to was another one I attempted to close and listed 2 other duplicates for... — Damien_The_Unbeliever, Feb 25 '11 at 11:49

score 1 · Answer 1 · answered Feb 25 '11 at 11:40

1

You could use a check constraint with a scalar UDF, like:

go
create function dbo.EnabledGroupCount(
    @GroupKey uniqueidentifier)
returns int as begin
    return (
    select COUNT(*) from TestTable where GroupKey = @GroupKey and EnableGroup = 1
    )
end
go
alter table TestTable add constraint chk_TestTable 
    check (dbo.EnabledGroupCount(GroupKey) in (0,1))

answered Feb 25 '11 at 11:40

Andomar

232,371
49
380
404

I just tried this in a 2008 instance, I inserted two rows with the same GroupKey, and both with EnableGroup=0. I then updated the entire table to set EnableGroup=1, and SQL Server let that go through. It won't let me add another row with the same groupid and EnableGroup either 0 or 1, so it's partially working, but not 100%. – Damien_The_Unbeliever Feb 25 '11 at 11:45
Scalar UDF's in check constraints tend to have issues with both snapshot isolation and multi row updates. – Martin Smith Feb 25 '11 at 11:54

score 1 · Answer 2 · answered Feb 25 '11 at 11:49

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

Add a constraint using T-SQL based on a condition

2 Answers2

Linked