Search code examples
sql-servercheck-constraints

Allow bit field to be set only if another bit field is set

I have two bit fields in a table and on update I need to allow the second field to bet set (to 1) only if the first field was already set.

So if a query updates the second field trying to set it to 1 the database must throw an error if the first field is not 1, from the other side, if the query updates both of these fields at once setting them both to 1 - it must work.

Maybe this can be done with check constraints.

Solution

  • Adding a table level CHECK CONSTRAINT should suffice.

    ALTER TABLE YourTable 
ADD CONSTRAINT CKC_BITS CHECK (FirstBit = 1 OR SecondBit = 0)