Two columns cannot be zero at the same time in sql

Question

Suppose 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 a restriction so that both addresses and alternateAddress cannot be null. ie the following cases are possible:

• The address is null and alternateAddress is not null.
• alternateAddress is null and the address is not null
• alternateAddress is not null and the address is not null.

But it cannot happen that any record in the Employee table inserted with alternateAddress and the address is null

Answer 1

Create a constraint for your table as follows:

 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 as follows:

 (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)