NetNews Usenet Archive 1993 #1/NN_1993_1.iso/spool/comp/database/informix/2888

Internet Message Format | 1993-01-07 | 5.4 KB

Path: sparky!uunet!haven.umd.edu!darwin.sura.net!zaphod.mps.ohio-state.edu!rpi!usc!elroy.jpl.nasa.gov!swrinde!emory!emory!not-for-mail
From: [e-mail] (Alan Popiel - 303/977-9998)
Newsgroups: comp.databases.informix
Subject: Re:  UPDATE problem on UNIQUEly INDEXed table on DOS
Date: 7 Jan 1993 13:28:08 -0500
Organization: Mailing List Gateway
Lines: 127
Sender: [e-mail]
Distribution: world
Message-ID: <[e-mail]>
Reply-To: [e-mail] (Alan Popiel - 303/977-9998)
NNTP-Posting-Host: emory.mathcs.emory.edu
X-Informix-List-ID: <list.1758>

->From: [e-mail] (Pedro Geraldes Freire)
->Subject: UPDATE problem on UNIQUEly INDEXed table on DOS
->Date: Wed, 6 Jan 1993 09:36:42 GMT
->Reply-To: [e-mail] (Pedro Geraldes Freire)
->Organization: Universidade Nova de Lisboa, PORTUGAL
->
->I'm having a problem running an UPDATE statement on a table. I
->understand the nature and reason of this problem however, I think there
->should be a way around it.
->
->I have this database table
->
->fartigos
->(
->    cod_1     char(8) not null,
->    c_int_g    smallint not null,
->    c_int_c    smallint not null,
->    c_int_art    smallint not null,
->    ...other data fields
->)
->
->with this single index
->
->unique index fartigos_ix on fartigos(cod_1, c_int_g, c_int_c, c_int_art)
->
->where the indexed fields are a composite key, and all these fields
->except for c_int_art are external keys. The c_int_art field acts as a
->local key and is also used to maintain an indexing on the records
->associated with the same (cod_1, c_int_g, c_int_c) data group, i.e. for
->each data group c_int_art must always range from 1 to N, where N is the
->number of records associated with that data group.
->
->This means that when a record must be inserted for a particular data
->group somewhere in the middle of  the sequence, say at position K such
->that  1 <= K <= N, the rows that have a c_int_art  >= K must have their
->c_int_art field incremented in order to open a "gap" for the incoming record.
->To perform such action, I use this SQL statement
->
->UPDATE fartigos
->   SET c_int_art = c_int_art + 1
->   WHERE cod_1 = ...
->         AND c_int_g = ...
->         AND c_int_c = ...
->         AND c_int_art >= K
->
->which should do the job. But it doesn't!
->When the statement is executed, it proceeds by looking up the first row
->that satisfies the condition, updating it and then repeats the process
->with the next row and so on, until there are no more rows to update. The
->question is that, when updating a row that has c_int_art = P and row P+1
->exists and hasn't been updated (yet), after the update you'll have 2
->rows P+1 and that violates the UNIQUE INDEX constraint, which causes the
->statement (and the program) to be aborted with the following error message:
->
->"Could not update a row in the table"
->SQL -346
->ISAM -100
->
->What annoys me about this is that:
->
->1. removing the UNIQUE constraint from the index is a poor (although
->simple) solution because the constraint makes sense and is rightly placed;
->
->2. because I somehow feel that a SQL statement shoud be atomic i.e. if
->the database is in a consistent state before the statement is carried
->out, and the statement is a valid and legal SQL statement which would
->render the database to a consistent state, then the way the statement is
->carried out (and the intermediate states it may generate) shouldn't
->interfere in its successful completion.
->
->Would anybody give me some hints, alternative solutions, advice, whatever?
->
->Thanks.
->
->Pedro.
->
->Pedro Geraldes Freire             | BITNET/Internet: [e-mail]
->Projecto CIMTOFI                   |            UUCP: [e-mail]
->UNINOVA - GRI - FCT/UNL           | Fax:   (+351) (1) 295 56 41/44 61
->2825 Monte Caparica, PORTUGAL     | Phone: (+351) (1) 295 44 64 ext.1560
->
->
Hello, Pedro,

This is Alan, again.

The solution proposed by Bharat Shah should work, but it involves two sets of
updates to the database.

*IF* you are working in 4GL, then you can try something like this, which only
does one set of updates:

DECLARE sel_cursor CURSOR FOR
SELECT ROWID, c_int_art
  FROM fartigos
 WHERE ...
   AND c_int_art >= K
 ORDER BY c_int_art DESC        { This is the trick to make this work. }

FOREACH sel_cursor INTO my_row_id, my_int_art
  UPDATE fartigos
     SET c_int_art = my_int_art + 1    { or whatever increment is needed }
   WHERE ROWID = my_row_id
END FOREACH

INSERT INTO fartigos
         ( ..., c_int_art )
  VALUES ( ..., K )

About what annoys you:
1. DON'T do this!!!  It will foul up your database seriously.  For example, first,
   c_int_art = 5 -> 6; then both 6 -> 7, then all three 7 -> 8, and it gets worse!
2. I understand your stand on the theory, but practical constraints sometimes keep
   our programs from approaching the theoretical ideal.  SQL is in theory
   non-sequential, but our computers must operate sequentially.

By the way, what is the English translation of "fartigos"?

Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel               | Internet: [e-mail]            |
| Martin Marietta, LSC         | ( Please note: My opinions do not   ) |
| P.O. Box 179, M/S 5422       | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179  | Voice: 303-977-9998                   |
+------------------------------+---------------------------------------+