SQL Server : how to force NOT NULL in a column only if another column in the same table is a specific value?

Question

我对 SQL 很陌生，因此我们将不胜感激。

我有下表:



我需要在 BusinessName 上强制 NOT NULL和 BusinessType ，但前提是 CustomerType = 'Business' .我已经在 CustomerType 上添加了一个约束。列使只有Business或 Personal将被接受。

如果用户想设置个人账户，在BusinessName中将接受NULL。和 BusinessType列。

Answer 1

像这样向现有表添加约束

ALTER TABLE Customers ADD CONSTRAINT Con_First check
(
      CustomerType = 'Business' AND BusinessName IS NOT NULL AND BusinessType IS NOT NULL
      OR CustomerType <> 'Business'  -- BusinessName and BusinessType can be null or not null, we omit AND here
)