Ограничение Postgres, обеспечивающее наличие одного столбца из многих?

Каковы хорошие способы добавления ограничения на PostgreSQL для проверки того, что ровно один столбец (из набора столбцов) содержит ненулевое значение?

Обновление. Вероятно, я хочу использовать выражение check, как описано в Создать таблицу, и Изменить таблицу.

Обновление: я просматриваю доступные functions.

Обновление: только для фона, вот логика проверки Rails, которую я сейчас использую:

validate :multi_column_validation
def multi_column_validation
  n = 0
  n += 1 if column_1
  n += 1 if column_2
  n += 1 if column_3
  unless 1 == n
    errors.add(:base, "Exactly one column from " +
      "column_1, column_2, column_3 must be present")
  end
end

Чтобы быть ясным, я ищу PSQL, а не Ruby, здесь. Я просто хотел показать логику, которую я использую, поскольку она более компактна, чем перечисление всех возможностей "таблицы истинности".

Ответы

Ответ 1

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

CREATE FUNCTION count_not_nulls(p_array anyarray)
RETURNS BIGINT AS
$$
    SELECT count(x) FROM unnest($1) AS x
$$ LANGUAGE SQL IMMUTABLE;

С помощью этой функции вы можете создать CHECK CONSTRAINT как:

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(array[col1, col2, col3]) = 1);

Это будет работать только в том случае, если столбцы имеют один и тот же тип данных. Если это не так, вы можете использовать их, например, в виде текста (поскольку вам просто нужен нулевой случай):

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(array[col1::text, col2::text, col3::text]) = 1);

Как хорошо помнят @muistooshort, вы можете создать функцию с вариативными аргументами, что позволяет сделать это:

CREATE FUNCTION count_not_nulls(variadic p_array anyarray)
RETURNS BIGINT AS
$$
    SELECT count(x) FROM unnest($1) AS x
$$ LANGUAGE SQL IMMUTABLE;

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK(count_not_nulls(col1, col2, col3) = 1);

Ответ 2

Вот элегантное решение из двух столбцов в соответствии с "constraint - тот или другой столбец, не имеющий значения null" Доска объявлений PostgreSQL:

ALTER TABLE my_table ADD CONSTRAINT my_constraint CHECK (
  column_1 IS NULL != column_2 IS NULL);

(Но вышеприведенный подход не может быть обобщен для трех или более столбцов.)

Если у вас есть три или более столбца, вы можете использовать подход таблицы истинности, иллюстрируемый a_horse_with_no_name. Тем не менее, я считаю следующее легче поддерживать, потому что вам не нужно вводить логические комбинации:

ALTER TABLE my_table
ADD CONSTRAINT my_constraint CHECK (
  (CASE WHEN column_1 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_2 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_3 IS NULL THEN 0 ELSE 1 END) = 1;

Чтобы скомпенсировать это, было бы полезно создать пользовательскую функцию, чтобы можно было снять шаблонный шаблон CASE WHEN column_k IS NULL THEN 0 ELSE 1 END, оставив что-то вроде:

(non_null_count(column_1) +
non_null_count(column_2) +
non_null_count(column_3)) = 1

Это может быть столь же компактным, как и PSQL (?). Тем не менее, я бы предпочел, если возможно, получить такой синтаксис:

non_null_count(column_1, column_2, column_3) = 1

Ответ 3

Как указано mu слишком коротко:

alter table t
add constraint only_one_null check (
    (col1 is not null)::integer + (col2 is not null)::integer = 1
)

Ответ 4

Немного неуклюжий, но должен сделать трюк:

create table foo
(
   col1 integer,
   col2 integer,
   col3 integer,
   constraint one_is_not_null check 
        (    (col1 is not null and col2 is null and col3 is null) 
          or (col1 is null and col2 is not null and col3 is null)
          or (col1 is null and col2 is null and col3 is not null)
        )
)