Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> DEFERRABLE check constraint on IOT not working?

DEFERRABLE check constraint on IOT not working?

From: Gabor <gabor.kecskemeti_at_yahoo.com>
Date: 3 Mar 2006 16:31:02 -0800
Message-ID: <1141432262.707799.199030@i39g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US