SQL: как обеспечить, чтобы только один столбец был задан в группе столбцов

В SQL существует ли способ обеспечить, чтобы только один столбец из группы столбцов имел значение, а остальные были пустыми? Может быть, ограничение или триггер? Этот тип вещей может быть для таблицы поиска, но есть ли альтернативные схемы таблиц, которые могли бы сделать это лучше?

Например:

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

Основная проблема заключается в том, что эти столбцы представляют собой все FK для других таблиц, поэтому я не могу свернуть их до одного столбца.

Я использую SQL Server, но любой ответ будет делать.

Ответ 1

@tvanfosson предложил ограничения работать нормально для трех столбцов, но для общности предпочитаю

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

потому что он лучше обобщается на любое количество столбцов с "линейно растущим" (вместо "квадратично растущего" ) количества кодирования (он даже опережает на SQL-диалектах, которые не требуют явного литья логического aka bit для int, но Я не уверен, что SQL Server является одним из них).

Ответ 2

Должно работать ограничение, такое как следующее:

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

Однако это не будет заставлять его содержать столбцы, отличные от нуля. Для этого добавьте еще одно ограничение:

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

Ответ 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))
)

Ответ 4

Для меня это выглядит плохое дизайнерское решение. Поскольку идентификатор является первичным ключом в этой таблице, это будет юридическое значение для всех отношений внешнего ключа. Это означает, что вам необходимо усердно работать на уровне интерфейса/бизнес-уровня, чтобы гарантировать, что значения находятся в допустимом диапазоне.

Например, способ настройки таблиц, для таблицы 2 абсолютно необходимо использовать 1 в качестве значения поиска вместо 2, которое предполагается использовать, - и база данных не будет ловить его.

Я бы, наверное, не поехал по этому маршруту. Я просто создам схему с именем lookups и создаю одну таблицу поиска для каждого значения поиска. Таким образом, база данных будет надлежащим образом применять все ограничения.

Как вы настроили таблицу поиска, в настоящее время вы ограничены целыми внешними ключами. В некоторых случаях это может быть не очень хорошая идея - например, вы хотите сохранить код страны/коды для состояния, а не целочисленные значения, представляющие их.

Ответ 5

Мне кажется, что вы хотите использовать один столбец для множества этих вещей. Возможно, вы можете использовать какой-то тег, чтобы сказать, что это Foo,3 или Bar,7 или Baz,9?