We help IT Professionals succeed at work.

How do I specify a SQL Server constraint to ensure one column is NOT NULL if another column has a certain value?

Suppose we have a table:

 (InvoiceId int...,
  RequiresDelivery bit NOT NULL,
  DeliveryAddressId int NULL CONSTRAINT FK_Invoice_Address_Delivery REFERENCES Address(AddressId)

If the column RequiresDelivery is set to 1 then the DeliveryAddressId needs to have a value, but if RequiresDelivery is set to 0, it is ok for DeliveryAddressId to be NULL.

How do I express this as a SQL constraint - or do I have to use a trigger?

This is for SQL Server 2012 and later.
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
The shortest code, albeit maybe not the clearest (then again, it is understandable, with some thought), is:

CHECK(RequiresDelivery = 0 OR DeliveryAddressId IS NOT NULL)
--or (RequiresDelivery = 0 OR DeliveryAddressId > 0)