Сложные ограничения целостности

Вопрос. Я написал на языке PL/SQL хранимую процедуру, но думаю, она не будет работать правильно с одновременным доступом к ней нескольких пользователей. Эта процедура проверяет, что строка с определенными значениями не существует и инициирует ошибку, если она существует. Если строка не существует, процедура выполняет некоторые вычисления, а затем вставляет строку. Я думаю, если другой пользователь вставил строку в эту же таблицу и зафиксировал эту вставку после того, как моя хранимая процедура выполнила проверку, но до того, как она зафиксировала вставку, то в таблицу могут быть вставлены неверные данные. Эта проблема не может быть устранена при помощи уникального ключа, поскольку поля не всегда должны быть различными – только иногда. Единственное решение, которое я смог придумать, состоит в том, чтобы проверять условие в начале хранимой процедуры, а затем снова проверять в конце, и в случае любой неудачной проверки выполнять откат. Есть ли лучший способ сделать это?

Таблица имеет три столбца: A, B и С; комбинация значений столбцов A и B всегда должна быть уникальной, если только значение столбца C не равно 'W'. Если в столбце C содержится значение 'W', то значения столбцов A и B могут совпадать со значениями в других строках. Кроме того, столбец B может иметь неопределенное значение (null). Можно ли создать ограничение для этого?

Ответ. Вы правы, при работе в многопользовательской среде могут быть проблемы. Фактически невозможно определять кросс-стороковые (это ваш случай) или кросс-табличные (типа внешних ключей) ограничения целостности. Почти всегда я вижу попытки реализовать это в кодах приложений или с помощью триггеров, что неправильно для многопользовательской среды. Как вы заметили, если два пользователя одновременно делают одно и тоже, они не видят работу друг друга. Чтобы достичь цели, нужно фактически сериализовать модификации этой таблицы, используя оператор LOCK TABLE.

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

ключи, которые содержат только значения NULL, не включаются в индексы типа "B-дерево". Если вы создаете индекс на T (C1, C2) и существует строка, в которой оба столбца C1 и C2 имеют значения NULL, то такая строка не помещается в индекс. Итак, вы можете в сервере Oracle селективно индексировать строки, если вы будете возвращать NULL-ключ (полностью NULL-ключ);

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

Итак, я создаю вашу таблицу:

SQL> create table t 2 ( a int, 3 b int, 4 c varchar2(1) not null 5 ); Table created.

И уникальный индекс по столбцам A и B, когда столбец C <> 'W':

SQL> create unique index t_idx 2 on t 3 ( case when c <> 'W' 4 then a 5 end, 6 case when c <> 'W' 7 then b 8 end 9 ); Index created.

Я получил именно то, что вам нужно: SQL> set feedback off SQL> insert into t values (1,1,'W'); SQL> insert into t values (1,1,'W'); SQL> insert into t values (1,1,'x'); SQL> insert into t values (1,1,'y'); insert into t values (1,1,'y') * ERROR at line 1: ORA-00001: unique constraint (T_IDX) violated

Значения NULL, NULL получаются тогда, когда столбец C = 'W', а значения столбцов A, B, когда столбец C <> 'W', то есть я уникально индексирую столбцы A,B только тогда, когда столбец C <> 'W' – в точности то, что вам нужно. Это как бы похоже на индексирование предложения WHERE.

Содержание раздела