AW: duplicate key error while inserting Data

Dirk Naujoks wrote:
> 
> Hi,
> 
> 
> We are inserting data into a table whith a serial-field.
> 
> This insert statement is set against the database using
> a java application.
> 
> Doing so sometimes the database sends an error
> INSERT INTO <table> (DS_ID, KEZ_ID, KEZ_ELEM_ID, TYPE_ID, REF_ID) VALUES
> ('6709A',9,5,14,7) :23000:[200] (at 102): Duplicate key
> 
> The table has got an other filed which is the serial-field and this field
> and the field DS_ID are the unique key.
> 
> OS is LINUX with  "2.4.21-243-athlon" kernel
> database version: 7.5.00.18 build 018-121-079-776
> JDBC-driver-version: 7.6.0   build 000-000-003-264
> 
> Is there anybody who had the same problem?
> 


Once we had an open source guy who (after checking the output of the vtrace) 
found, that for some reason, a second insert was done in an 
applications-subprocedure he had forgotten. After cleaning that sub-routine in 
the application, he had no trouble any more.

If you managed to fill the serial column with higher values than the current 
serial value, then (as I wrote for another serial-question in the mailing list 
today) do:

Assuming a table t1 with serial column s, having the current value of 1000,
You can 'set' the serial value using this:

Select max(s) from t1  --> finding the 1000
Insert t1 (s,<all other mandatory columns>) values (1001, ....)
Delete t1 where s = 1001

Meaning: if a serial-column's value is given by hand and greater than the 
current serial value the user-given value will be used as new max value, 
meaning the next implicit serial value will be 1 greater then the user-given 
one.

If you always have a mixture of user-given values (sometime being smaller than 
the max serial value), then you always cause the possibility of duplicate keys.
Then using serial column is no good decision.
For a first reset after some filling from somewhere the method given above will 
help.

But in your case there is another question:
As serials will be unique (if no user-inserts for this column are done 
afterwards with smaller values than the current maximum serail value), why do 
you combine another column with the serial column to build the primary key?

Elke
SAP Labs Berlin


> 
> Mit freundlichen Grüßen
> i.A.
> Dirk Naujoks
> 
> --
> Dirk Naujoks
> IOn Aktiengesellschaft
> Tel.: +49 (0211) 924 95 - 120
> Fax: +49 (0211) 924 95 - 111
> Mail: <mailto: naujoks@xxxxxx>
> Web: www.ion.ag
> 
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:    http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx