Post Made Community Wiki by Jack Douglas
2 added 53 characters in body
source | link

SQL Server 2000, 2005:

You can take advantage of the fact that only one null is allowed in a unique index:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

for 2000, you may need SET ARITHABORT ON (thanks to @gbn for this info)

SQL Server 2005

You can take advantage of the fact that only one null is allowed in a unique index:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

SQL Server 2000, 2005:

You can take advantage of the fact that only one null is allowed in a unique index:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

for 2000, you may need SET ARITHABORT ON (thanks to @gbn for this info)

1
source | link

SQL Server 2005

You can take advantage of the fact that only one null is allowed in a unique index:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);