Re: Timing Issues in Oracle

From: Jozef Starosta <josef_at_mars.softouch.bc.ca>
Date: 1995/07/11
Message-ID: <3tut7n$efg_at_mars.softouch.bc.ca>

Gordon E. Hooker (gordonh_at_acslink.net.au) wrote:
: Hi All,
 

: I am fairly new to the Oracle fold after many years working with
: Informix.
 

: I have a question I would like to pose to anyone out there who can
: advise me.
 

: I would like to know how the Oracle server implements the relational
: concept of timing with regards to constraint checking.
 

: In a transaction if I wanted to update data on which there is an
: unique constraint how would you defer the constraint. What I mean is
: as follows:
 

: I have a set of rows
 

: col1 unique
: 1
: 2
: 3
: 4
: I want to update the table and set col1 = col1 + 1. When the value 1
: is update and set to 1+1 it would cause unique constraint error. How
: could I defer constraint checking until all the rows had 1 added to
: it, which at the end of the update not cause the error?
 

: Thanks in advance...

Hi Gordon,

try this:

SQL> create table A (a1 integer, a2 char(1), unique(a1)); Table created.
SQL> insert into A values(1, 'X');
1 row created.
SQL> insert into A values(3, 'X');
1 row created.
SQL> insert into A values(2, 'Y');
1 row created.
SQL> insert into A values(4, 'Y');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from A;

                       Softouch Information System Report
        A1 A                                                                    
---------- -                                                                    
         1 X                                                                    
         3 X                                                                    
         2 Y                                                                    
         4 Y

/*----------------------------------------------*/
/* Now let us try what you have asked about ... */
/*----------------------------------------------*/                                                                    

SQL> update A set a1=a1+1;
4 rows updated.

SQL> commit;
Commit complete.
SQL> select * from A;

                       Softouch Information System Report
        A1 A                                                                    
---------- -                                                                    
         2 X                                                                    
         4 X                                                                    
         3 Y                                                                    
         5 Y   

/*-----------------------------------------------------------------------*/

/* But it works ?!?! Probably because I have defined a TABLE constraint */ /* and that one is probably checked after finishing the UPDATE statement */

/* if I would try a 'partial' update, like this... */                                                                  

SQL> update A set a1=a1+1 where a2='X';
update A set a1=a1+1 where a2='X'

       *
ERROR at line 1:
ORA-00001: unique constraint (JOJO.SYS_C002783) violated

/* ...it fails. May be this is your case. */ /* But if I disable the constraint ... */

SQL> alter table A disable unique(a1);
Table altered.
SQL> update A set a1=a1+1 where a2='X';
2 rows updated.
SQL> update A set a1=a1+1 where a2='Y';
2 rows updated.

/* ... everything works */
SQL> commit;
Commit complete.

/* now back to costraint ... */
SQL> alter table A enable unique(a1);
Table altered.
SQL> select * from A;

                       Softouch Information System Report
        A1 A                                                                    
---------- -                                                                    
         3 X                                                                    
         5 X                                                                    
         4 Y                                                                    
         6 Y                                                                    

SQL> insert into A values (5,'Y');
insert into A values (5,'Y')

            *
ERROR at line 1:
ORA-00001: unique constraint (JOJO.SYS_C002783) violated

/* of course, the costraint is in effect */

Note: To disable specific constraint you can use

alter table A disable constraint sys_c002783;

Hope it helps.

Jozef Received on Tue Jul 11 1995 - 00:00:00 CEST