用于限制列组合中的NULL值的约束

Question

我正在尝试创建一个带有主键的表。例如，下面是表格结构

+--------+----------+------+-----------+--------------+
| ID     | NAME     | SOLD | PURCHASED | MANUFACTURED |
+--------+----------+------+-----------+--------------+
| 522345 | ProductA | 23   |   NULL    | 45           |
+--------+----------+------+-----------+--------------+
| 502345 | ProductB | NULL | 56        |   NULL       |
+--------+----------+------+-----------+--------------+

在这里，我必须确保所有3列(SOLD/PURCHASED/MANUFACTURED(都不应为NULL。但是任何1或2列都可以为NULL。如何为这种情况创建约束？请提出建议。我已经尝试了下面的查询，但它需要所有3列的值。我没什么主意了。

CREATE TABLE MYTABLE (
ID VARCHAR(20) NOT NULL,
NAME VARCHAR(50),
SOLD INT NOT NULL,
PURCHASED INT NOT NULL,
MANUFACTURED INT NOT NULL,
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED 
(
[ID] ASC,
[SOLD] ASC,
[PURCHASED] ASC,
[MANUFACTURED] ASC
)
)

Answer 1

您可以尝试CHECK CONSTRAINT

CREATE TABLE MYTABLE (
ID VARCHAR(20) NOT NULL,
NAME VARCHAR(50),
SOLD INT,
PURCHASED INT,
MANUFACTURED INT,
CONSTRAINT CHK_NULL CHECK ((SOLD IS NOT NULL) OR (MANUFACTURED IS NOT NULL) OR (PURCHASED IS NOT NULL))
)

Answer 2

使用触发器回滚事务，如下所示：

create trigger dbo.check_insert_null_items on dbo.MYTABLE after insert as
begin
if exists ( select * from inserted where SOLD is null and PURCHASED is null and MANUFACTURED is null)
begin
rollback transaction
raiserror ('error: ... ', 16, 1)
end
end