主页 / dba / 问题 / 5278
Accepted
David Clarke
Asked: 2011-09-01 16:46:22 +0800 CST

检查约束只有三列之一是非空的

  • 772

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

这样做的背景是试图改进将非数字结果捕获到现有系统中的能力。向表中添加两个新列并带有约束以防止每行出现多个结果是最经济的方法，但不一定是正确的方法。

更新：对不起，数据类型混乱。遗憾的是，我并不打算将指示的结果类型解释为 SQL Server 数据类型，只是通用术语，现在已修复。

sql-server constraint

4 个回答

  1. Best Answer

    以下应该可以解决问题：

    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
    • 98

  2. 您可能需要在约束内进行三个测试，一个测试用于您希望为 null 的每一对，一个用于测试不应该为 null 的列：

    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)
);
    • 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);
    • 5

  4. 对于 PostgreSQL

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

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

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

相关问题