在sql中，两列不能同时为null

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:

现在我想放置约束,使地址和alternateAddress都不能为空,可能的情况是:

address is null and alternateAddress is not null

address为null,alternateAddress不为null

alternateAddress is null and address is not null

alternateAddress为null,address不为null

alternateAddress is not null and address is not null

alternateAddress不为null,地址不为null

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

但不能发生Employee表中的任何记录与alternateAddress一起插入并且地址都为null

4 个解决方案

#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

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

#3

With De Morgan's Law, not (A and B) is equivalent to (not A or not B), therefore

根据De Morgan定律,因此,(A和B)不等同于(不是A或不是B)

(address is not null OR alternateAddress is not null)

#4

Can you not use:

你不能用:

IF(COALESCE(address, alternateAddress) IS NOT NULL)

WHERE (COALESCE(address, alternateAddress) IS NOT NULL)

Or is that bad form?

或者是那种糟糕的形式?

#1