SQL Server 2008: Check constraints that guarantees that only one value in all rows is set to 1 and others are 0

Question

There is a need to build constraint on the column that guarantees that only one value in all rows is 1 and all the others are 0.

Solution with triggers exists but I would like to have something built in.

Is such thing possible at all?

Answer 1

Edit

Actually I just noticed you are on SQL Server 2008 you could use a filtered index for this

CREATE UNIQUE NONCLUSTERED INDEX UIX ON YourTable (col) where col = 1

Original Answer

The easiest way would probably be to store this one special pk in a separate one row table. The no more than one row aspect can be enforced with check constraints.

CREATE TABLE OneRowTable
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
OtherTablePK int
);

Otherwise assuming you might have an id field comprised of positive integers you could add a computed column with the following definition

case when col=1 then -1 else id end

and add a unique constraint to that.