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 -> Re: Deferred constraint question

Re: Deferred constraint question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 25 Jun 1999 19:23:13 GMT
Message-ID: <3778d653.89524579@newshost.us.oracle.com>


A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Fri, 25 Jun 1999 14:59:55 +0100, you wrote:

>
>Strange - the following demo suggests it works
>(event with dbms_sql.v7 rather than the 'native'
>that another user proposed).
>
>The output from the SQL is interspersed, with
>a couple of extra comments.
>
>

[snip]

>
>
>set constraint jpl_ck_n1 immediate;
>*** *
>*** ERROR at line 1:
>***ORA-02290: check constraint (DEMO.JPL_CK_N1) violated
>*** Change it back to immediate, and Oracle finds the bad row
>*** Note: it is not supposed to do anything but report it just yet.
>

why do you say that? Its reporting an error saying "i cannot set the constraint to immediate because there is data in there that doesn't work with immediate".

It hasn't performed the "set constraint xxx immediate" statement -- the constraint is still deferred.

>rem
>rem AND SURELY THIS IS AN ERROR, or has the constraint
>rem not become immediate because that state is inconsistent
>rem with the bad data already in place ?
>rem
>
>insert into jpl_demo values(11);
>
>*** 1 row created.
>*** BUT the constraint was set to immediate !!
>*** I expected the row to be rejected
>
>
>select * from jpl_demo;
>
>*** N1
>*** ---------
>*** 10
>*** 11
>
>commit;
>*** *
>*** ERROR at line 1:
>*** ORA-02091: transaction rolled back
>*** ORA-02290: check constraint (DEMO.JPL_CK_N1) violated
>***
>*** Thebad rows cause a rollback when you try to commit.
>***

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 25 1999 - 14:23:13 CDT

Original text of this message

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