Как создать столбец Postgres, указывающий, есть ли ссылка на строку
У меня есть схема, похожая на
CREATE TABLE A(
id INTEGER PRIMARY KEY NOT NULL,
has_ref BOOLEAN NOT NULL
);
CREATE TABLE B(
f_id INTEGER REFERENCES A (id)
);
CREATE TABLE C(
f_id INTEGER REFERENCES A (id)
);
http://sqlfiddle.com/#!17/224c86
has_ref должен быть TRUE, если строка в A имеет ссылку, и FALSE, если нет. На каждую строку в A должна быть не более одной ссылки.
В идеале я хотел бы, чтобы это происходило автоматически и применялось базой данных, так что, например. если строка в B или C удалена, has_ref изменится обратно на FALSE в указанной строке.
Я не мог найти способ сделать это с помощью триггеров или ограничений.
Ответы 2
Вы можете использовать запрос, чтобы определить его:
select a.*
(exists (select 1 from b where b.f_id = a.id) or
exists (select 1 from c where c.f_id = a.id)
) as has_ref
from a;
Если вы хотите инкапсулировать эту логику, вы можете создать представление, которое вычисляет ее на лету.
@НадавЗингерман. . . К сожалению, это изменчивые данные, и, чтобы быть точными, их необходимо рассчитывать на лету. Если вам нужно только изображение значения при вставке строки, вы можете написать триггер для вычисления.
Я понимаю. Я надеялся на решение, которое будет хранить эти данные без их пересчета каждый раз.
@НадавЗингерман. . . Как можно «хранить» данные? Ссылочные таблицы могут измениться, и тогда необходимо будет изменить значение has_cref. Я полагаю, вы могли бы реализовать эту логику с помощью триггера, но это кажется более сложным, особенно потому, что логика exists будет довольно быстрой с индексами во вторичных таблицах.
Вместо того, чтобы хранить boolean, сохраните столбец integerrefcount.
Затем добавьте триггеры на b и c, которые добавляют или вычитают из этих значений всякий раз, когда ссылочная строка добавляется, удаляется или изменяется.
Это решило бы проблему поддержания этих полей в актуальном состоянии, но не обеспечило бы их правильность, как ограничение. Например, UPDATEвключение refcount без изменения B или C будет разрешено.
Да, но у вас не может быть ограничений для этого. Так что лучшего решения нет.
Это сработает только в
SELECT, верно? Я хочу, чтобы это хранилось как часть таблицы, а не вычислялось при каждом запросе, так как все задействованные таблицы довольно велики в производстве.