Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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
share|improve this question

marked as duplicate by Mark Storey-Smith, Nick Chammas, dezso, ypercube, Max Vernon Apr 18 '13 at 16:15

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Do you mean you want just one row in a table to be un-editable by updates? –  RoKa Apr 18 '13 at 8:51

2 Answers 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.

share|improve this answer
    
Well, in PG it can be done by a partial unique index: CREATE UNIQUE INDEX idx_allow_only_one_true ON some_table (bool_column) WHERE bool_column; This makes it quite a viable option. –  dezso Apr 18 '13 at 9:28
    
@dezso In MSSQL we have Filtered Indexes. It's the same concept. –  spaghettidba Apr 18 '13 at 9:34
    
Remus, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now. –  AlexKuznetsov Apr 18 '13 at 12:41

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.
share|improve this answer
    
+1 I would do the same thing. In fact, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now. –  AlexKuznetsov Apr 18 '13 at 12:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.