Two columns can not be null at the same time in sql

Question

Suppose there a table called

Employee

ID number, name varchar(24 char), address varchar2(100 char), alternateAddress(100 char), sex varchar2(10 char)

Now I want to put constraint such that both address and alternateAddress cannot be null i.e possible cases are:

• address is null and alternateAddress is not null
• alternateAddress is null and address is not null
• alternateAddress is not null and address is not null

But cannot happen that any record in Employee table inserted with alternateAddress and address both null

Answer 1

Create a constraint to your table like this:

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_OneAddress] CHECK  ((NOT [address] IS NULL) OR (NOT [alternateAddress] IS NULL))
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [CK_OneAddress]
GO

Answer 2

Create your constraint like this:

(address is null and alternateAddress is not null) or 
(alternateAddress is null and address is not null) or 
(alternateAddress is not null and address is not null)