Sokrates on Oracle

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.


11 Responses to “Prevent Validation of Check Constraints in”

  1. Certainly a remarkable bug.

    For your information, there is a workaround described further down in the bug description: Add a INSERT or DELETE clause to the MERGE statement.

    Also, there is already an one-off patch for Linux x64 available and backports for other versions / platforms can be requested


  2. Dom Brooks said

    Oooh – that’s a bad one.


  3. […] blog for articles about some of the nastier, and sometimes unbelievable, bugs in the Oracle code. Here’s one (that’s just about forgivable in terms of failing to test) published by Sokrates a couple of […]

  4. the on off patch is online installable. No downtime required.

  5. hourim said

    mhouri@mhouri> select * from v$version;

    Oracle Database 11g Enterprise Edition Release – Production
    PL/SQL Release – Production
    CORE Production
    TNS for 32-bit Windows: Version – Production
    NLSRTL Version – Production

    mhouri@mhouri> merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

    1 ligne fusionnée.

    mhouri@mhouri> commit;

    Validation effectuée.

    mhouri@mhouri> spool off;

  6. […] Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT” […]

  7. […] […]

  8. […] I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore […]

  9. I am getting lot of problems due to this and waiting for the oracle release – where they promised to fix this.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: