CODEWITHSUNDEEP
sqlsql-server
laurie
laurie

Reputation: 6251

Check constraint for a flag column

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

Upvotes: 1

Views: 1609

Answers (3)

Martin Smith
Martin Smith

Reputation: 453212

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)

Upvotes: 1

Mostafa Elmoghazi
Mostafa Elmoghazi

Reputation: 2154

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

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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)

Upvotes: 1

Related Questions