SQL Check Constraint that requires a not null value in a column based on the value of another column
You want to enforce the implication:

(OnSite=true) =&gt; (ClientId is not null)


This can be rewritten as:

(OnSite=false) or (ClientId is not null)


Your constraint therefore becomes:

CHECK ( OnSite=0 or ClientId is not null)