SQL: how to enforce that only a single column is set in a group of columns

Question

In SQL, is there a way to enforce that only one column out of a group of columns has a value, and the others are null? Maybe a constraint or trigger? This type of thing might be for a lookup table, but are there any alternate table designs that might accomplish this better?

For example:

ID    OtherTable1ID    OtherTable2ID    OtherTable3ID
-----------------------------------------------------
1     23               NULL             NULL
2     NULL             45               NULL
3     33               55               NULL   -- NOT ALLOWED

The main issue is that these columns are all FKs to other tables, so I can't collapse them down to a single column.

I'm using SQL Server, but any answer will do.

Answer 1

@tvanfosson's suggested constraints work OK for three columns, but for generality I prefer

(cast(col1 is not null, int) +
 cast(col2 is not null, int) +
 cast(col3 is not null, int)) = 1

because it generalizes better to any number of columns with "linearly growing" (instead of "quadratically growing") amount of coding (it's even neater in SQL dialects that don't require explicit casting of boolean aka bit to int, but I'm not sure if SQL Server is one of those).

Answer 2

A constraint such as the following should work:

 (column1 is null and column2 is null)
   or (column1 is null and column3 is null)
   or (column2 is null and column3 is null)

This won't force it to contain a non-null column, however. To do that add another constraint:

 column1 is not null
    or column2 is not null
    or column3 is not null

Answer 3

CREATE TABLE Repro.Entity
(
    entityId INTEGER IDENTITY (1, 1) NOT NULL,
    column1 INTEGER,
    column2 INTEGER,
    column3 INTEGER,
    CONSTRAINT Entity_PK PRIMARY KEY(entityId),
    CONSTRAINT Entity_CK CHECK(
        (column1 IS NOT NULL AND column2 IS NULL AND column3 IS NULL) OR 
        (column1 IS NULL AND column2 IS NOT NULL AND column3 IS NULL) OR 
        (column1 IS NULL AND column2 IS NULL AND column3 IS NOT NULL))
)