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

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 2

It sounds to me as if you want to be using one column for the set of those things. Perhaps you can use some sort of tag to say that it's a Foo,3 or a Bar,7 or a Baz,9?

Answer 3

@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 4

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))
)

Answer 5

For me it looks like a bad design decision. Since ID is the primary key in this table, it will be a legal value for all foreign key relationship. This means you have to work extra hard in the front end/business layer to guarantee that the values are within accepted range.

For example, the way the tables are set up, it is absolutely legal for table 2 to use 1 as the lookup value instead of 2 it is supposed to use - and database will not trap it.

I'd probably won't go this route. I will just simply create a schema named lookups and will create one lookup table per lookup value. In this way the database will properly enforce all the constraint.

The way you have set up the lookup table, you are currently limited to integer foreign keys. In some cases it might not be a good idea - for example you'd like to store the country code/codes for state rather than the integer values representing them.