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:

CREATE TABLE Invoice
 (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.
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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)