CODEWITHSUNDEEP
t-sqlconstraints
user634013
user634013

Reputation: 1

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]

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.

Upvotes: 0

Views: 2861

Answers (2)

Andreas Ågren
Andreas Ågren

Reputation: 3929

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

Upvotes: 1

Andomar
Andomar

Reputation: 238048

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))

Upvotes: 1

Related Questions