Re: Err 239 on Insert to serial column

Stuart, we got this error once in our NT development database. (It has never happened in production (Solaris). I don't know if the platform has anything to do with it or not.) Our code always inserts the serial with 0, yet one day it hiccuped on us. Checked the database to see if there was a conflicting row. There was not. Tried it outside our app with an SQL statement with 0 -- still didn't work. Had to do just what you did -- advance the serial "absolutely." Like I said, it only happened once. William Raper wrote: > > A 239 error is a duplicate key on a unique index. > > When a serial data type is used, a unique index is created on the column. > > As you know, when you insert the value 0 into a serial column, it is > supposed to take the "next available" value and use it. > As you also know, when you insert a value other than 0 into a serial column, > that value is used. > > If you force a value other than the "next available" value, then later > change your habits to use 0, you will eventually "catch up" to the value > that you forced and the result is a duplicate key. > > The moral of the story is this: if you are using a serial datatype, always > insert the value 0 or have logic to react to the possibility of a duplicate > key. > > -----Original Message----- > From: Stuart Worrall [mailto:Stuart@sworrall.freeserve.co.uk] > Sent: Wednesday, October 13, 1999 6:44 PM > To: informix-list@iiug.org > Subject: Err 239 on Insert to serial column > > Hello, > > I have recently been presented with a problem whereby the error 239 is > generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX > VALUES (0,"DDF",1,"etc") where the first column is the serial. > > There is a unique index on the column which was dropped and re-created with > no success. > > After further investigation the serial value was found to be 70650. I then > used the following statement to force the insert.. INSERT INTO XXXXX > VALUES (70651,"DDF",1,"etc") which worked. > > After this, using the value 0 to insert into the serial field worked and I > was able to put in the values of 70652, 70653 etc. > > Has anybody seen this problem anywhere else? > > The system is Dynamic Server 7.3.UC8 running on a HP > > Thanks in advance > > Stu Worrall