我有一个（SQL Server）表，其中包含3种类型的结果：FLOAT，NVARCHAR（30）或DATETIME（3个单独的列）。我想确保对于任何给定的行，只有一列具有结果，其他列为NULL。实现此目的的最简单的检查约束是什么？

上下文正试图改进将非数字结果捕获到现有系统中的能力。最经济的方法是在表中添加两个带有约束的列，以防止每行不止一个结果，但不一定是正确的方法。

更新：对不起，数据类型为snafu。可悲的是，我不打算将指示的结果类型解释为SQL Server数据类型，而只是一般术语，现在已修复。

#1 楼

以下应该可以解决问题：

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO


评论

通过使用IN，可以将其进一步推广为“全部为空或不为空”。示例：CHECK（IIF（ConsentDate为null，1，0）+ IIF（ConsentingUserID为null，1，0）+ IIF（ConsentingClientIdentifiers为null，1，0）在（0，3）中）

–米奇
20-11-17在16:20

#2 楼

您可能需要在约束内进行三个测试，对每个想要为空的对进行一次测试，对不应该为空的列进行一次测试：

ALTER TABLE table
ADD CONSTRAINT CK_one_is_null
CHECK (
     (col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL)
  OR (col2 IS NOT NULL AND col1 IS NULL AND col3 IS NULL) 
  OR (col3 IS NOT NULL AND col1 IS NULL AND col2 IS NULL)
);


评论

这不是那么可伸缩，我有一个带有9个外键的表，只有一个不应该为null，我更喜欢@MarkStoreySmith的解决方案

–阿米尔（Amir Pashazadeh）
19年8月25日在21:27


#3 楼

这是使用内置数组函数的PostgreSQL解决方案：

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK (array_length(array_remove(ARRAY[col1::text, col2::text, col3::text], NULL), 1) = 1);


评论

这将比以前分别由Mark Storey和mrdenny发布的CASE或AND / OR解决方案在postgreSQL中实现更快吗？

–克里斯·布里特（Chris Britt）
18年6月12日在13:35

#4 楼

FOR POSTGRESQL 

CHECK( (col_1 IS NOT NULL)::integer + (col_2 IS NOT NULL)::integer + ... = 1 )


我们用IS NOT NULL（真或假）将列转换为布尔值，然后转换为:: integer（0或1）
然后可以使用算术运算符

= 1  //must one row is not null  
<= 1 //only one row can be not null


评论

问题是关于SQL Server通过。您的答案仅在Postgres中有效。

–超立方体ᵀᴹ
20-2-14在19:23
@ypercubeᵀᴹMicrosoft SQL Server。误解是由他们的混乱营销引起的。

–peterh-恢复莫妮卡
20-2-14在19:50
@ peterh-ReinstateMonica不确定您所指的是什么。当两种产品都使用20年以上时，如何将PostgreSQL与SQL Server混淆？

–超立方体ᵀᴹ
20-2-14在20:33


答案是好的。只是不针对特定标签的这个问题。

–超立方体ᵀᴹ
20-2-14在20:34
@ypercubeᵀᴹSQL表示查询语言。 SQL Server是处理SQL语言请求的进程。有许多不同的SQL Server产品，PostgreSQL是其中之一。微软也有一个SQL Server产品，他们称它为“ SQL Server”而不是“ Microsoft SQL Server”，这是一种肮脏的营销手段。另外一个答案对我来说看起来有点像PostgreSQL，但我不确定。

–peterh-恢复莫妮卡
20-2-14在20:37