Столкнулись со странным поведение Oracle 11.2.0.3.
Нарушается DEFERRABLE CHECK CONSTRAINT при использовании MERGE with DELETE. Поиск в интернете и на support.oracle.com к успеху не привёл.
Создаём и заполняем таблицу.
drop table tmp__mrg purge;
create table tmp__mrg (
id number(15) primary key
, count_opers number(15) not null
, sum_turn number(15) not null deferrable
);
insert into tmp__mrg (id, count_opers, sum_turn)
select null id, null count_opers, null sum_turn from dual where null is not null
union all select 1, 1, 1 from dual
union all select 2, 2, 2 from dual;
commit;
Вывод:
Table dropped.
Table created.
2 rows created.
Commit complete.
Теперь воспроизвожу ошибку.
Запускаю скрипт:
set constraints all deferred;
merge into tmp__mrg snp
using (
select null id, null count_opers, null sum_turn from dual where null is not null
union all select 1, 10, 10 from dual
union all select 2, 0, null from dual
union all select 3, 30, 30 from dual
) src on (src.id = snp.id)
when matched then
update
set snp.count_opers = src.count_opers
,snp.sum_turn = src.sum_turn
delete where snp.count_opers = 0
when not matched then
insert (id, count_opers, sum_turn)
values (src.id, src.count_opers, src.sum_turn);
-- Убедились, что в таблице данные корректные
select * from tmp__mrg;
-- Сейчас сломаемся
commit;
Вывод:
Constraint set.
3 rows merged.
ID COUNT_OPERS SUM_TURN
---------- ----------- ----------
1 10 10
3 30 30
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (GWH_SBDM.SYS_C0046565) violated
Если использовать MERGE без DELETE, а удаление выполнить отдельной командой, то всё работает.
set constraints all deferred;
merge into tmp__mrg snp
using (
select null id, null count_opers, null sum_turn from dual where null is not null
union all select 1, 10, 10 from dual
union all select 2, 0, null from dual
union all select 3, 30, 30 from dual
) src on (src.id = snp.id)
when matched then
update
set snp.count_opers = src.count_opers
,snp.sum_turn = src.sum_turn
when not matched then
insert (id, count_opers, sum_turn)
values (src.id, src.count_opers, src.sum_turn);
-- Убедились, что в таблице данные пока не корректные
select * from tmp__mrg;
delete tmp__mrg snp where snp.count_opers = 0;
-- Убедились, что в таблице данные корректные
select * from tmp__mrg;
-- Сейчас всё будет ок
commit;
Вывод:
Constraint set.
3 rows merged.
ID COUNT_OPERS SUM_TURN
---------- ----------- ----------
1 10 10
2 0
3 30 30
1 row deleted.
ID COUNT_OPERS SUM_TURN
---------- ----------- ----------
1 10 10
3 30 30
Commit complete.
Интерес не праздный, такой MERGE with DELETE выдаёт DBMS_SNAPSHOT, когда я пытаюсь выполнить FAST REFRESH мат. представления с агрегированием (SUM). Из-за наличия у нас в СУБД ограничения, что SUM-поле является NOT NULL (суммируются тоже непустые величины), обновления мат. представления ломается, даже если отложить проверку ограничения (сделать его DEFERRABLE).
Хочется проверить поведение в других версиях СУБД, либо кто-то мог столкнуться с похожей проблемой и найти её решение. :)