Re: Deferred constraint question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 25 Jun 1999 14:59:55 +0100
Message-ID: <930319442.19861.0.nnrp-01.9e984b29@news.demon.co.uk>

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.

drop table jpl_demo;
create table jpl_demo(

     n1 number,
     constraint jpl_ck_n1 check (n1 = 25) deferrable initially immediate

);

insert into jpl_demo values(10);
*** *

• ERROR at line 1:
• ORA-02290: check constraint (DEMO.JPL_CK_N1) violated
• As expected - the constraint is IMMEDIATE
  ***
  

declare

     v_junk pls_integer;
     n pls_integer;
begin
     n := dbms_sql.open_cursor;
     dbms_sql.parse (n, 'set constraint jpl_ck_n1 deferred', dbms_sql.v7);
     v_junk := dbms_sql.execute(n);
     dbms_sql.close_cursor(n);

end;
/

insert into jpl_demo values(10);

• 1 row created.
• The constraint has been deferred, so 10 is no longer illegal

select * from jpl_demo;

• N1
• ---------
• 10
• And here it is

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.

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.
  ***
  

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jim Jenkinson wrote in message ...
>That was my thought as well, but, although the statement parses and executes
>without error, the constraint remains in immediate mode. Received on Fri Jun 25 1999 - 08:59:55 CDT