home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.informix
- Path: sparky!uunet!mcsun!inesc.inesc.pt!dec4pt.puug.pt!unl!hara.fct.unl.pt!pgf
- From: pgf@hara.fct.unl.pt (Pedro Geraldes Freire)
- Subject: UPDATE problem on UNIQUEly INDEXed table on DOS
- Message-ID: <1993Jan6.093642.7764@fct.unl.pt>
- Keywords: problem, UPDATE, UNIQUE INDEX, integrity constraints
- Sender: news@fct.unl.pt (USENET News System)
- 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
- Lines: 75
-
- 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
-
-