11.2.0.2 has a great new “feature”: you can disable the validation of a check contraint during an update !
how does it work ?
it is a bit like magic: when an update violates a check constraint, just use a merge-statement to update the table.
See:
sokrates@11.2.0.2 > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));
Table created.
sokrates@11.2.0.2 > insert into oops values(1, 2);
1 row created.
sokrates@11.2.0.2 > insert into oops values(2, 4.5);
1 row created.
sokrates@11.2.0.2 > commit;
Commit complete.
sokrates@11.2.0.2 > update oops set n = n * 7 where i = 1;
update oops set n = n * 7 where i = 1
*
ERROR at line 1:
ORA-02290: check constraint (SOKRATES.CH_N_SM_5) violated
so far, so good. now the trick:
sokrates@11.2.0.2 > merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;
1 row merged.
sokrates@11.2.0.2 > REM oops !!!
sokrates@11.2.0.2 > select i, n from oops;
I N
———- ———-
1 14
2 4.5
sokrates@11.2.0.2 > commit;
Commit complete.
Now, isn’t that a great new feature ?
What does Oracle say ?
Bug 9285259: MERGE IGNORES CHECK CONSTRAINT
says:
“
…
Fixed in Product Version 12.1
…
WORKAROUND: ----------- none
“
So, in my opinion, if you use “merge” to update rows, you have a real problem and probably your best decision could be “don’t use 11.2 at all, wait until they have fixed that behaviour in 12.1″
Remarks.
I can reproduce the Bug on 11.2.0.2 on Linux x86-64
According to the metalink note mentioned above, it affects also 11.2.0.1, which I haven’t checked.
Furthermore, I don’t know if it’s only specific to that platform.
