Добавьте ограничение, чтобы сделать столбец уникальным для каждой группы строк.

В таблице Postgres есть столбец status, который может принимать только два значения: Active и Inactive.

Один из столбцов называется userid. В таблице может быть несколько строк с одним и тем же userid, но не более одной из них может быть status = 'Active'. Мне нужен только один или ни одного status как Active на userid. Как я могу создать ограничение с этим условием? Я не смог найти никакой помощи в документации Postgres.


postgresql database constraints unique-index partial-index
person Shashwat Kumar    schedule 28.12.2015    source источник
comment
Лучше добавить столбец IsActive логического типа вместо хранения VARCHAR или, если у вас больше статусов, используйте тип INT и добавьте таблицу поиска.   -  person Lukasz Szozda    schedule 28.12.2015
comment
Это лучшая альтернатива, но я не могу ее изменить, так как вся структура базы данных следует за ней.   -  person Shashwat Kumar    schedule 28.12.2015
comment
Итак, может ли быть больше записей в этой таблице с одним и тем же идентификатором пользователя? В чем смысл/предназначение этой таблицы, какой-то журнал состояния?   -  person joop    schedule 28.12.2015
comment
@joop аналог статуса чата. Один пользователь может сохранить несколько статусов в базе данных, но может использовать только один за раз.   -  person Shashwat Kumar    schedule 28.12.2015
comment
Я не знаю, что такое статус чата. Вы можете добавить некоторые примеры данных к своему вопросу, чтобы показать, что у вас есть и что вы хотите.   -  person joop    schedule 28.12.2015

Ответы (1)


arrow_upward
16
arrow_downward

Например, @lad2025 прокомментировал, status действительно должен быть boolean. Дешевле, чище.

В любом случае вы можете навязать свое правило с помощью частичного уникального индекса:

Чтобы разрешить ноль или одну строку с status = 'Active' во всей таблице:

CREATE UNIQUE INDEX tbl_active_uni ON tbl (status)
WHERE status = 'Active';

Чтобы разрешить ноль или одну строку с status = 'Active' на userid, сделайте userid индексируемым столбцом:

CREATE UNIQUE INDEX tbl_userid_active_uni ON tbl (userid)
WHERE status = 'Active';

Обратите внимание, что userid IS NULL не вызовет уникальных нарушений, потому что два значения NULL никогда не считаются равными. userid в этом случае должно быть set NOT NULL.

Почему индекс, а не ограничение?

Ответ на ваш вопрос в комментарии: это индекс, а не CONSTRAINT.

Индекс для первого случая крошечный, содержит одну строку или не содержит ее.
Индекс для второго случая содержит одну строку для каждого существующего userid, но это < em>самый дешевый и быстрый способ, а также чистый и безопасный. В любом случае вам понадобится индекс для проверки других строк, чтобы сделать это быстро.

Вы не можете иметь проверку ограничения CHECK для других строк - по крайней мере, не в чистом и надежном виде. Есть способы, которые я бы не рекомендовал в этом случае:

Если вы используете ограничение UNIQUE для (userid, status) (которое также реализуется с помощью уникального индекса в фоновом режиме!), вы не можете сделать его частичным, и все комбинации принудительно уникальный. Вы можете по-прежнему использовать это, если работаете с status IS NULL во всех случаях, кроме случая 'Active'. Но на самом деле это потребует гораздо большего индекса, включающего все строки.

person Erwin Brandstetter    schedule 28.12.2015
comment
Хотя решение работает, но разве это не индекс вместо ограничения. Я имею в виду, что создание индекса использует дополнительную память для сохранения данных столбца в упорядоченном виде. Можно ли использовать индексы вместо ограничений? - person Shashwat Kumar; 28.12.2015
comment
@ShashwatKumar: см. добавленное объяснение выше. - person Erwin Brandstetter; 28.12.2015
comment
Решение, кажется, не работает. Я добавляю две строки с одинаковым идентификатором пользователя и статусом «Неактивный», но при второй вставке он дает ошибку дублирующего ключа. Когда я проверяю информацию таблицы, используя \d, отображается... tb1_userid_key UNIQUE CONSTRAINT, btree (userid) . С этим условием очевидно, что один и тот же идентификатор пользователя не будет принят в таблице. - person Shashwat Kumar; 29.12.2015
comment
@ShashwatKumar: с уникальным ограничением all combinations are enforced to be unique. Вместо этого я предлагаю использовать частичный уникальный индекс. Пожалуйста, прочтите еще раз. - person Erwin Brandstetter; 29.12.2015
comment
Я использовал ту же команду, которую вы дали. Результатом этого является единственное ограничение. - person Shashwat Kumar; 29.12.2015
comment
@ШашватКумар. CREATE UNIQUE INDEX ... не создает CONSTRAINT. tb1_userid_key должен был быть создан каким-то другим способом. - person Erwin Brandstetter; 29.12.2015