SQL Server 2008 Unique Key Constraint

Question

I have a table call test and have four fields. i have create composit unique key on the table for which i want my records to be unique. we are tracking the previous versions of records and marking all the old records isdeleted=1, But the Problem is, my unique Key will not allow me same records with two or more isdeleted=1

Create table test (ApplicationID int,IsDeleted bit
                  CONSTRAINT test_uck UNIQUE (ApplicationID,IsDeleted)                
                  )
go
insert into test values(1,0)
insert into test values(1,1)
insert into test values(1,1)

It looks like you are inserting a duplicate value. Surely it's working as expected? — ta.speot.is, Jan 18 '13 at 07:12

score 1 · Answer 1 · answered Jan 18 '13 at 07:14

Is this what you want:

Create table test (
    ApplicationID int not null,
    IsDeleted bit not null
)
create unique index IX_UniqueApplications on test (ApplicationID)
     where IsDeleted=0
go
insert into test values(1,0)
insert into test values(1,1)
insert into test values(1,1)

It's called a Filtered Index. You didn't want the combination of ApplicationID and IsDeleted to be unique - you just wanted ApplicationID to be unique if it wasn't deleted. Those are two quite different concepts.

score 0 · Answer 2 · answered Jan 18 '13 at 07:15

Try adding a version number field

vNum int

to your table then expand your primary key to include that field.

Since you are keeping track of previous versions of the record anyway, you could simply take the last version number for that record and increase it by one for the next.

SQL Server 2008 Unique Key Constraint

2 Answers2