Re: Unique Indexes

->Date: Wed, 19 May 93 09:04:45 -0400 ->From: David Nguyen <ge!davidn%davidn@uunet.UU.NET> ->To: informix-list@rmy.emory.edu ->Subject: Unique Indexes -> ->I have a table in which there's a unique index created, and it involves 9 columns. -> ->I'm having a problem when I goto $select from it in one of my *.ec files. ->The sqlca.sqlcode returned is 100 (SQLNOTFOUND). -> ->When I looked it up in the ESQL manual, the ISAM error reads: ->"there is already a record with the same value in a unique key." ->and the Corrective Action specified: ->"Check that you did not attempt to add a duplicate value to a column with ->a unique index by way of iswrite, isrewrite, isrewcurr, or isaddindex." One problem here is that you are confusing SQL status 100 with ISAM error 100. It is unfortunate and confusing that these two conditions have the same number, but they are really not related. sqlca.sqlcode = SQLNOTFOUND (100) is a fairly normal condition, in which you just don't find what you are looking for. ISAM error 100 results from an added or updated row trying to violate theunique-key requirements for a table. This is the underlying condition for sqlca.sqlcode = -239 (Could not insert new row - duplicate value in a UNIQUE INDEX column.) Note that sqlca.sqlcode *errors* have negative numbers, while the *normal* conditions are non-negative: 0 for okay, and 100 for not found. -> ->I'm puzzled because all I'm trying to do is to select a row out of this ->table, in which I've granted permission to select, update, insert, delete, ->and index on. Here's what I did: -> -> ->$select col10, col11, col12, col13, col14, col15, col16 -> into $col10, $col11, $col12, $col13, $col14, $col15, $col16 -> -> from tablename -> where col1 = $ptr->col1 and -> col2 = $ptr->col2 and -> col3 = $ptr->col3 and -> col4 = $ptr->col4 and -> col5 = $ptr->col5 and -> col6 = $ptr->col6 and -> col7 = $ptr->col7 and -> col8 = $ptr->col8 and -> col9 = $ptr->col9; -> ->The 'unique' index key includes col1 - col9. I don't understand why ->prior to putting in this 'select' statement in my routine, I had an ->$update statement to the same table based on the same set of columns, ->and I didn't get this SQLNOTFOUND returned. I tried to debug this through ->softbench and was able to look at all the fields prior to calling the ->ESQL routine and after, but softbench skips over the ESQL stuff. So ->prior to calling this routine, I went into isql and was able to verify ->that the row exists in the table that matches the select where clause. I am puzzled by this, as well. If you find the row using ISQL, then you should be able to find the row using ESQL. I wonder if the fact that the key has nine columns is the culprit. SE engines support 8-part indexes, at least at version 4.0, while OnLine engines support 16-part indexes. Is it possible that ESQL is not handling the ninth part of the index correctly, maybe not being as up-to-date as your engine? -> ->Any insights would help me out greatly. Thanks. -> ->############################################################## ->David Nguyen Internet: davidn@geis.geis.com ->GE Information Services UUCP: uunet!ge!davidn ->401 North Washington Street Voice: 1-301-340-5461 ->Rockville, MD 20850 USA ->############################################################## -> Are you in the part of GE that merged with Martin Marietta? I still can't tell the players apart without a program. Regards, Alan +------------------------------+---------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | 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 | +------------------------------+---------------------------------------+