Only allow one checked row in a Column in SQL Server

Question

Background:

I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL.

Question:

Is is possible to have a boolean column in a table that only allows one checked row. So even if a user wanted to update the table with another checked row they wouldn't be able to? Suggestions and Ideas and use layman terms please.

Attempted illustration:

ID        Col_1        Col_2       Boolean_Col
----------------------------------------------
1         Data       More Data         0
2         Data       More Data        -1
3         Data       More Data         0

Answer 1

In SQL Server the data type that resembles the most to Boolean is bit and the values allowed are 1,0 and NULL:

CREATE TABLE #test (
    ID int,
    Col_1 varchar(10),
    Col_2 varchar(10),
    Boolean_Col bit
)

That said, you have some options:

• Filtered Unique Index

  CREATE UNIQUE NONCLUSTERED INDEX UQ_Boolean_Col ON #test(Boolean_Col) WHERE Boolean_Col = 1;

• Indexed View

  You can see a sample of this technique here. In this case it wouldn't do anything different from a filtered index, but it would work in SQL Server 2005 (filtered indexes were introduced in 2008)

• Scalar function in CHECK constraint

  A summary of this technique can be found here. I discourage the use of this technique, because it would be incredibly slow. BTW, sorry for linking my blog again.

• Trigger

  I'm not a big fan of this solution because, again, it would be slow and non-declarative. However, it would/could work.

If you're interested in using the filtered index, here's a script to demonstrate how it would work:

-- test table using the definition you posted
CREATE TABLE #test (
        ID int,
        Col_1 varchar(10),
        Col_2 varchar(10),
        Boolean_Col bit
)


-- create the filtered unique index
CREATE UNIQUE NONCLUSTERED INDEX UQ_Boolean_Col 
    ON #test(Boolean_Col) 
    WHERE Boolean_Col = 1;

-- insert the sample data you provided
INSERT INTO #test VALUES(1,'Data','More Data',0)
INSERT INTO #test VALUES(2,'Data','More Data',1)
INSERT INTO #test VALUES(3,'Data','More Data',0)

-- Insert one more row that violates the constraint
INSERT INTO #test VALUES(4,'Data','More Data',1)

-- Msg 2601, Level 14, State 1, Line 2
-- Cannot insert duplicate key row in object 'dbo.#test' with unique index 'UQ_Boolean_Col'. The duplicate key value is (1).
-- The statement has been terminated.

Answer 2

No

While strictly speaking it can be achieved, it is always a bad idea. In general thinking at restricting properties of sets is a bad road to travel because set property enforcement usually require inspecting the entire set to validate the constraint. In other words enforcing your 'single row check' requires to scan every other row, on every change. This is not only slow, more importantly is the 100% guaranteed recipe for deadlocks.

It would be much better if you post your actual problem rather than ask how to implement the solution you choose.