Check Constraint with Filtering Logic

I have a table that has bit column called “Flag” it does not allow NULL. A requirement here is to ALWAYS have Flag=1 set for every Id in the table below but only 1 time per each unique Id and Flag columns combination. At the same time all other rows can be have Flag=0 set multiple times if there are more than 1 entry.

Basically SUM of Flag grouped by ID should always be 1.

I though of unique constraint on the Id and Flag fields but since Flag=0 can be set multiple times for the same combination this cannot be used.

Any suggestions?

— current dataset

drop table if exists #test;
go
create table #Test (Id_pk int identity(1,1) not null, id int not null, Flag bit not null)
Insert into #Test (id, Flag) 
values (12, 0), (12,0), (12, 0), (12,0),  (12, 1), (12,1), (13,0), (13, 0), (14,1), (14,1), (20,1),  (20,0), (30,1),  (40,0)
select * from #Test

— desired outcome

drop table if exists #test;
go
create table #Test (Id_pk int identity(1,1) not null, id int not null, Flag bit not null)
Insert into #Test (id, Flag) 
values (12, 0), (12,0), (12, 0), (12,0),  (12, 0), (12,1), (13,0), (13, 1), (14,0), (14,1), (20,1),  (20,0), (30,1),  (40,1)
select * from #Test

Answer

You are not looking for a check constraint. You want a filtered unique constraint:

create unique index unq_test_id_flag
    on #test(id)
    where flag = 1;

Here is a db<>fiddle.