Unique Constraint Question
-
Is it possible to creat a unique constraint like: Col1, Specific Value Col2?
I have inherited a user table with Col1 = user ID (which can be recyled) and Col2 = Active Flag(Binary)
I want to create a constraint so that each active ID is unique, but I don't care how many inactive rows I have for a given ID. Is this possible?
-
Using rule.
-
Trigger also can do this.
-
alter function fn_UniqueActive(@p1 int,@p2 int)
returns int
as
begin
declare @retVal int
if @p2 = 1
begin
set @retVal = (select count(*) from t1 where (f1 = @p1 and f2 = @p2))
end
else
begin
set @retVal = 1
end
return @retVal
end
if exists (select * from sysobjects where name = 't1')
drop table t1
create table t1(f1 int, f2 int, CHECK(dbo.fn_UniqueActive(f1,f2) = 1))
--first time entry
insert into t1 values(1,1)
insert into t1 values(2,1)
insert into t1 values(3,1)
insert into t1 values(4,1)
insert into t1 values(5,1)
--all these can pass
insert into t1 values(3,2)
insert into t1 values(3,2)
insert into t1 values(3,2)
insert into t1 values(4,2)
insert into t1 values(4,2)
insert into t1 values(5,2)
--none of these should pass
insert into t1 values(1,1)
insert into t1 values(2,1)
insert into t1 values(4,1)
-
I would add a column for Inactive Date or something and allow it to be null. The make the Uniqeu Constraint
Col1, Col2 and InactiveDate
this will allow you to have the exact scenario you need with better auditing and might even alreay exist. In fact you can actually then get rid of the Active flag.
-
Anteres you need ot come back more often to this forum... I hadn't learned something new 2 posts in a row for quite a months.
Thanx again.
-
Always busy these days. Try to pop out every so often.
-
Need help?? Just hire a junior DB into the team.
Where do I send my resume?
-
I don't do the hiring, sorry.
-
Thanks for all the help everyone.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply