From: pgf@hara.fct.unl.pt (Pedro Geraldes Freire)
Subject: UPDATE problem on UNIQUEly INDEXed table on DOS 
Keywords: problem, UPDATE, UNIQUE INDEX, integrity constraints 
Reply-To: pgf@hara.fct.unl.pt (Pedro Geraldes Freire)
Organization: Universidade Nova de Lisboa, PORTUGAL
Date: Wed, 6 Jan 1993 09:36:42 GMT

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: pgf@fct.unl.pt
Projecto CIMTOFI		  |            UUCP: pgf@unl.uucp
UNINOVA - GRI - FCT/UNL           | Fax:   (+351) (1) 295 56 41/44 61
2825 Monte Caparica, PORTUGAL     | Phone: (+351) (1) 295 44 64 ext.1560