Блог

Итак, я пытался разобраться с этим, но, похоже, что последняя строка (проверка) не разрешает вложенные запросы в ней. Есть какой-нибудь способ заставить это работать 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: Триггеры

Наиболее упрощенным подходом было бы установить триггер на TANK, который запрашивает TANK и выдает исключение, если УРОВЕНЬ превышает ЕМКОСТЬ. Проблема с такого рода упрощенным подходом, однако, заключается в том, что практически невозможно правильно обрабатывать проблемы параллелизма. Если сессия 1 уменьшает ЕМКОСТЬ, затем сессия 2 увеличивает УРОВЕНЬ, а затем обе транзакции фиксируются, триггеры не смогут обнаружить нарушение. Это может и не быть проблемой, если одна или обе таблицы редко изменяются, но в целом это будет проблемой.

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

Проблему параллелизма можно решить, создав материализованное представление ПРИ ФИКСАЦИИ, которое объединяет TANK и таблицу TANKS, а затем создав контрольное ограничение для материализованного представления, которое проверяет, что УРОВЕНЬ <= ЕМКОСТЬ. Вы также можете избежать двойного сохранения данных, если материализованное представление будет содержать только данные, которые нарушали бы ограничение. Для этого потребуются журналы материализованного просмотра в обеих базовых таблицах, что немного увеличит накладные расходы на вставки (хотя и меньше, чем при использовании триггеров). Перевод проверки во время фиксации решит проблему параллелизма, но это создает небольшую проблему с управлением исключениями, поскольку операция фиксации теперь может завершиться неудачей из-за сбоя обновления материализованного представления. Ваше приложение должно быть способно справиться с этой проблемой и предупредить пользователя об этом факте.

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

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

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

Функция, которую вы ищете, называется SQL assertions, и она еще не реализована в Oracle 12c

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

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