AW: Sync serial value after manually inserted records

Erik Rehrmann [mailto:rehrmann@xxxxxx]
> Gesendet: Freitag, 8. April 2005 12:47
> An: maxdb@xxxxxxxxxxxxxxx
> 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/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