SQL-Server: Define columns as mutually exclusive

Question

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?

Answer 1

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.