DEFERRABLE check constraint on IOT not working?

Gabor

unread,

Mar 3, 2006, 7:31:02 PM3/3/06

to

I have managed to make really small test case.
So, lets's test it on a heap table first:

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 4 01:23:08 2006

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> CREATE TABLE T (P NUMBER PRIMARY KEY, A NUMBER);

Table created.

SQL> ALTER TABLE T ADD CONSTRAINT CT CHECK (A>0) DEFERRABLE;

Table altered.

SQL> SET CONSTRAINT CT DEFERRED;

Constraint set.

SQL> INSERT INTO T VALUES (1,-1);

1 row created.

SQL> DELETE FROM T;

1 row deleted.

SQL> SET CONSTRAINT CT IMMEDIATE;

Constraint set.

SQL> COMMIT;

Commit complete.

Everything working as intended. Same example, but with IOT:

SQL> CREATE TABLE T (P NUMBER PRIMARY KEY, A NUMBER) ORGANIZATION
INDEX;

Table created.

SQL> ALTER TABLE T ADD CONSTRAINT CT CHECK (A>0) DEFERRABLE;

Table altered.

SQL> SET CONSTRAINT CT DEFERRED;

Constraint set.

SQL> INSERT INTO T VALUES (1,-1);

1 row created.

SQL> DELETE FROM T;

1 row deleted.

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?

Jonathan Lewis

unread,

Mar 4, 2006, 2:35:05 AM3/4/06

to

"Gabor" <gabor.ke...@yahoo.com> wrote in message
news:1141432262.7...@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

Gabor

unread,

Mar 4, 2006, 6:39:21 PM3/4/06

to

I don't think it's because delete doesn't "realy" deletes the row, or
not just that. Try the same with UPDATE instead of DELETE (update "a"
to correct > 0 value), the same error happens..

Anyway, I can't raise a TAR, because I'm not a customer (as you can
see, I use 10gXE). Or is there way for XE users to report bugs too?

Jim Kennedy

unread,

Mar 4, 2006, 8:43:47 PM3/4/06

to

"Gabor" <gabor.ke...@yahoo.com> wrote in message

news:1141515561.4...@i39g2000cwa.googlegroups.com...

I believe you can pay for support for XE.
Jim

Michel Cadot

unread,

Mar 5, 2006, 1:13:08 AM3/5/06

to

"Gabor" <gabor.ke...@yahoo.com> a écrit dans le message de news: 1141515561.4...@i39g2000cwa.googlegroups.com...

Update in an index (or IOT) is not really an update but delette+insert.
So you're in the same problem.

Regards
Michel Cadot