Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> DEFERRABLE check constraint on IOT not working?
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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? Received on Fri Mar 03 2006 - 18:31:02 CST