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
Advertisement
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.