Check constraint for a flag column

Question

Database is MS SQLServer

Data example:

| Name | defaultValue |  value   |
| one  |     true     | valone   |
| one  |     false    | valtwo   |
| one  |     false    | valthree |

I'm after a way of constraining the table such that each 'Name' can only have one row with 'defaultValue' set to true

Answer 1

Create a computed column like this:

  ALTER TABLE yourtable
  ADD ValueCheck AS CASE defaultValue
     WHEN true THEN 1
     WHEN false THEN NULL
  END

and then add unique constraint for (Name, ValueCheck)

Answer 2

You can use a TRIGGER to validate this constraint on update or insert events and roll back the transaction if it was invalid.

Answer 3

I liked Michael's idea but it will only allow you one false value per name in SQL Server. To avoid this how about using

  ALTER TABLE yourtable
  ADD [ValueCheck] AS 
     (case [defaultValue] when (1) then ('~Default#?@') /*Magic string!*/
                 else value  end) persisted

and then add unique constraint for (Name, ValueCheck).

I am assuming that name, value combinations will be unique. If the value column does not allow NULLs then using NULL rather than the magic string would be preferable otherwise choose a string that cannot appear in the data (e.g. 101 characters long if the value column only allows 100 chars)