Re: DEFERRABLE check constraint on IOT not working?

"Gabor" <gabor.kecskemeti_at_yahoo.com> wrote in message news:1141432262.707799.199030_at_i39g2000cwa.googlegroups.com...
> SQL> SET CONSTRAINT CT IMMEDIATE;
> SET CONSTRAINT CT IMMEDIATE
> *
> ERROR at line 1:
> ORA-02290: check constraint (LORENAI.CT) violated
>
> Is this normal? It's some undocumented restricton (or documented, but
> hard to find), or a bug?
>

Just a funny follow-up.

I was running your test case, and at the end, I tried to switch to the sys account (for reasons that aren't significant). This is what happened on both 10.2.0.1 and 9.2.0.6

++++++++++++++++++

• your test running

1 row deleted.

SQL> SET CONSTRAINT CT IMMEDIATE;
SET CONSTRAINT CT IMMEDIATE
*

SQL> connect / as sysdba
ERROR:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TEST_USER.CT) violated

ERROR:
ORA-24313: user already authenticated

Warning: You are no longer connected to ORACLE.

+++++++++++++++++++

I'd call it a bug and raise an SR.

Possibly it's happening because the rowindex entry is cleared on the heap table, so the row ceases to exist, whereas the IOT is an index, so the delete simply marks as 'flagged for delete' and is therefore (accidentally) still visible as a potentially bad entry.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html