Constraints in SQL Database

Question

I need to have a table in T-SQL which will have the following structure

KEY     Various_Columns       Flag
 1          row 1              F
 2          row_2              F
 3          row_3              T
 4          row_4              F

Either no rows, or at most one row can have the Flag column with the value T. My developer claims that this can be achieved with a check constraint placed on the table.

Questions:

1. Can such a constraint be placed on the database itself (ie an inter-row constraint) at the database level, rather than in business rules for updating or inserting rows
2. Is such a table in normal form?
3. Or would normal form require removing the Flag column, and instead (say) had another simple table or variable containing the value of row which had Flag=T, ie in the above case row=3.

Answer 1

1 No. A check constraint is per row. No other constraint will do this either.

You need one of:

• a trigger (all versions)
• indexed view with filter Flag = T, and unique index on Flag (SQL Server 2000+)
• filtered index (SQL Server 2008)

2 Good enough

3 Overkill really. You're splitting the same data up to avoid one the solutions above. But using a one row table, FK for the ID columns, and a unique constraint on Flag