Использование подзапроса в инструкции Check в Oracle

Итак, я пытался это обработать, но кажется, что последняя строка (проверка) не позволяет использовать в ней подзапросы. Любой способ сделать эту работу Oracle?

CREATE TABLE Tank (
    n_id            int,
    day             date,
    level           int,
    CONSTRAINT pk_w_td PRIMARY KEY (n_id,day),
    CONSTRAINT fk_w_td_tan FOREIGN KEY (n_id) REFERENCES Tanks ON DELETE CASCADE,
    CHECK (level > 0 AND level <= (SELECT capacity FROM Tanks WHERE Tanks.n_id = TanksDay.n_id))
);

Вот информация об ошибке:

Error at Command Line:7 Column:32 Error report: SQL Error: ORA-02251: subquery not allowed here
02251. 00000 -  "subquery not allowed here"
*Cause:    Subquery is not allowed here in the statement.
*Action:   Remove the subquery from the statement.

Ответы

Ответ 1

Существует три основных способа решения этой проблемы, поскольку ограничения CHECK не могут быть основаны на запросе.

Вариант 1: Триггеры

Самый простой подход - это поставить триггер на TANK, который запрашивает TANKS и генерирует исключение, если LEVEL превышает CAPACITY. Проблема с подобным упрощенным подходом заключается в том, что почти невозможно корректно обрабатывать проблемы concurrency. Если сеанс 1 уменьшает CAPACITY, тогда сеанс 2 увеличивает уровень, а затем обе транзакции фиксируют, триггеры не смогут обнаружить нарушение. Это может быть проблемой, если одна или обе таблицы редко изменяются, но в целом это будет проблемой.

Вариант 2: материализованные представления

Вы можете решить проблему concurrency, создав материализованное представление ON COMMIT, которое присоединяется к таблице TANK и TANKS, а затем создает ограничение CHECK для материализованного представления, которое проверяет, что LEVEL <= CAPACITY. Вы также можете избежать хранения данных дважды, если материализованное представление содержит только данные, которые будут нарушать ограничение. Для этого потребуются материализованные журналы просмотра на обеих базовых таблицах, которые добавят немного накладных расходов для вставки (хотя и меньше, чем использование триггеров). Нажатие проверки на время фиксации решит проблему concurrency, но она представляет собой небольшую проблему с управлением исключениями, так как операция COMMIT может завершиться неудачно, потому что материализованное обновление представления не сработало. Ваше приложение должно будет иметь возможность справиться с этой проблемой и предупредить пользователя об этом.

Вариант 3. Изменение модели данных

Если у вас есть значение в таблице A, которое зависит от предела в таблице B, это может указывать на то, что предел в B должен быть атрибутом таблицы A (вместо или в дополнение к атрибуту таблицы B), Конечно, это зависит от специфики вашей модели данных, но ее часто стоит рассмотреть.

Ответ 2

Нет, к сожалению, ограничения CHECK не могут содержать подзапросы - см. документация.

Ответ 3

У Джастина есть несколько хороших идей. Еще один способ заключается в том, чтобы обернуть все вставки/обновления в таблицу пакетом (TAPI, если хотите) и реализовать там проверки. Вам необходимо убедиться, что все приложения используют ваш TAPI. Вам также потребуется реализовать некоторую пользовательскую блокировку, чтобы защитить ограничение от эффектов одновременной работы.

Ответ 5

Вам, вероятно, понадобится создать триггеры и использовать RAISE_APPLICATION_ERROR, если он находится за пределами допустимого диапазона.