Erik Rehrmann [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 8. April 2005 12:47 > An: [email protected] > Betreff: Sync serial value after manually inserted records > > Hi all, > > I've encountered a very serious problem using serials. > > A MaxDB is first filled via an ETL-process as a copy of a different DBMS, > i.e. all tables containing serial columns are filled not by the MaxDB > number generator, but by a manual insert. > > The source database is already used and, because of some normal deletions, > the serial ids are not subsequent. > > Using the new filled MaxDB our application gets lots of "Duplicate key > error", because the next generated serial id is already in use within the > table. > > So here are the questions: > > +) Is it possible to synchronize the max value of a serial with the > highest value of the serial id within the table? > > +) Is there any way, maybe as dba, to change the current serial value > within the cataloge? > > +) Is there any other way to use serials for tables wich values can also > be manually inserted?
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. Elke SAP Labs Berlin > > Environment is > - Linux / WindowsXP SP2 > - MaxDB version: 7.5.00.18 build 018-121-079-776 > - JDBC-driver-version: 7.6.0 build 000-000-003-264 > > Different combinations of other (newer/older) versions are also tested > without success. > > Any help is greatly appreciate. > > -- > Erik Rehrmann > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
