6

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

1
  • 3
    What did you try? Which DBMS are you using, SQL Server? – Colin 't Hart Jun 5 '14 at 19:49
7

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)
2
  • Someone posted and then deleted their comment. but I caught it and used it. It is the same logic. Thanks for the help. Check (NOT ([OnSite]=(1) AND [ClientId] IS NULL)) – SneakyPeet Jun 6 '14 at 8:35
  • Yes, the transformation is known as De Morgan's law – Lennart Jun 6 '14 at 8:43
1

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;

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.