Subject Re: [firebird-support] New auto-increment column, existing data
Author Helen Borrie
On 15/11/2016 19:58, 'Some One' anotherpersonsomewhere@...
[firebird-support] wrote:

> I am trying to add new auto-increment primary keys for existing table.
> This is what I have:
> ======================================
> set term ~ ;
> execute block
> as
> declare dbkey char(8);
> begin
> for
> select rdb$db_key
> from mytable
> into :dbkey
> do
> begin
> ...
> end
> end~
> set term ; ~
> ======================================
> This fails with error "malformed string", it looks like either
> rdb$db_key does not match char(8) or maybe it is related to same
> character set settings. Any suggestions?

This is totally the wrong way to go about achieving an
auto-incrementing key. The "field" rdb$db_key should never be used as
a basis for anything persistent, as it is not stable. It is
internally maintained by the engine according to some algorithm that I
forget, related to the offset position of the record on disk and a few
other things that cannot be assumed to be persistent. Read
rdb$db_key within a transaction, if you have a reason to, for that is
its only guaranteed "lifespan". Never try to write to it nor use it as
as a base for anything that is relied on for integrity.

Use the method Alan suggested or, if you are using Firebird 3, you can
define your PK field using the IDENTITY data type (which can be any of
the supported integer types and is maintained by an internal,
non-user-acessible generator).

> It also seems that when new entries are added the auto-incremented
> values will start from 0, causing conflicts. Is it ok to simply right
> click the generator in FlameRobin, select "set value" and enter a value
> higher than the highest existing or will that cause any trouble?

Using your method, it will invite a world of trouble. Using a proper
method, you can set a generator (or sequence) or an IDENTITY to a
specific value that will be treated as the most recently-generated
value. How you do it depends on the Firebird version, i.e.,
pre-Firebird 3 or post-Firebird 3.