Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are told causes too much overhead. But someone is suggesting unique index with where clause. I want to make sure I understand all of the pros and cons. Or is there a different better way to achieve this? This is for in house applications. Some people have also said to not bother putting anything in database, just make sure the application prevents it.

Edit: The column is a bit column that will not allow nulls. The table itself should be < 50 rows total. But only 1 can have this particular column marked true.

  • we need to ensure only 1 record has bit checked. What is datatype of this field. What is FULL list of values for it? – Akina Feb 24 '20 at 12:40
  • 2
    A unique filtered index is a good approach, IMHO. Just make sure you use the required set options. – Dan Guzman Feb 24 '20 at 12:42
  • 1
    "we are told it causes too much overhead" What kind of overhead? – Yannick Liekens Feb 24 '20 at 13:08
  • Can you provide a table definition (CREATE TABLE statement), and some sample data (INSERT statements) that demonstrates what is allowed and what is not. As an alternative, use dbfiddle.uk or similar – Lennart Feb 24 '20 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.