SQL-Server：将列定义为互斥 [英] SQL-Server: Define columns as mutually exclusive

问题描述

我想起了一个有趣的场景：SQL Server中是否有可能定义一个表，以便通过标准手段（约束等），我可以确保两个或更多列是互斥的？

我的意思是：我可以确保只有一列包含一个值吗？

解决方案

是的，可以使用CHECK约束：

  ALTER TABLE YourTable 
 ADD CONSTRAINT ConstraintName CHECK（col1为null或col2为null）
  

根据您的评论，如果许多列是独家，你可以这样检查：

  case col1为null然后0 else 1 end + 
 case col2为null然后0 else 1 end + 
 case，当col3为null然后0 else 1 end + 
 case当col4为null然后0 else 1 end 
 = 1 
  

这表示四列之一必须包含一个值。如果它们都可以为NULL，只需检查< = 1 。

joking with a collegue, I came up with an interesting scenario: Is it possible in SQL Server to define a table so that through "standard means" (constraints, etc.) I can ensure that two or more columns are mutually exclusive?

By that I mean: Can I make sure that only one of the columns contains a value?

解决方案

Yes you can, using a CHECK constraint:

ALTER TABLE YourTable
ADD CONSTRAINT ConstraintName CHECK (col1 is null or col2 is null)

Per your comment, if many columns are exclusive, you could check them like this:

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 +
case when col4 is null then 0 else 1 end
= 1

This says that one of the four columns must contain a value. If they can all be NULL, just check for <= 1.

这篇关于SQL-Server：将列定义为互斥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！