SQL Check Constraint that requires a not null value in a column based on the value of another column

Question

I have a table with the following two columns

• OnSite BIT NOT NULL
• ClientId INT NULL

I want to add a constraint that requires ClientId to be NOT NULL when OnSite is true(1).

I could not find anything on the site.

Thanks

Answer 1

You want to enforce the implication:

(OnSite=true) => (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)

Answer 2

For SQL Server you can use this:

CREATE TABLE dbo.MyTable (
    OnSite BIT NOT NULL,
    ClientID INT NULL);

ALTER TABLE dbo.MyTable WITH CHECK ADD CONSTRAINT CK_MyTable_ClientId_NotNull CHECK (OnSite=0 OR ClientId IS NOT NULL);
ALTER TABLE dbo.MyTable CHECK CONSTRAINT CK_MyTable_ClientId_NotNull;