How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this:

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that it can only be set on a single row.

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)

StackExchange Database Administrators Q#4815, answer score: 16