Sokrates on Oracle

Archive for March, 2011

Prevent Validation of Check Constraints in

Posted by Matthias Rogel on 27. March 2011 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.


sokrates@ > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));

Table created.

sokrates@ > insert into oops values(1, 2);

1 row created.

sokrates@ > insert into oops values(2, 4.5);

1 row created.

sokrates@ > commit;

Commit complete.

sokrates@ > 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@ > 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@ > REM oops !!!

sokrates@ > select i, n from oops;

I        N
———- ———-
1       14
2      4.5

sokrates@ > commit;

Commit complete.

Now, isn’t that a great new feature ?

What does Oracle say ?



Fixed in Product Version 12.1


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”


I can reproduce the Bug on on Linux x86-64

According to the metalink note mentioned above, it affects also, which I haven’t checked.

Furthermore, I don’t know if it’s only specific to that platform.

Posted in 11.2, Bug | 11 Comments »