> On Nov 15, 2016, at 3:58 PM, 'Some One' anotherpersonsomewhere@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
>
> I am trying to add new auto-increment primary keys for existing table.
> .....
> select rdb$db_key
> from mytable
> into :dbkey
> .....

As several people have written generators or sequences are the right way to create unique values for primary keys. They can be started at a value of your choice. The syntax depends on the Firebird version but checking the release notes or on-line documentation for Generator or Sequence will get you the right answer.

The rdb$db_key is a unique identifier for a record at a particular time, but is not stable over time or across backup/restore cycles. If you store the rdb$db_key as your primary key, eventually you will will get duplicate key errors when you try to store new records. For the hard core, the rdb$db_key uniquely identifies a record in a table - different records in different tables will have the same rdb$db_key. It's a three part value. The first part is the sequence of the pointer page for the table that holds the actual database page number that holds the record. Pointer pages are pages that hold an array of page numbers - pretty simple. The second part is the offset on the pointer page of the actual data page number. The third part is the identity of the index on that data page that describes the location and length of the record. (Records are stored with run-length compression, so their length varies.)

When a record is deleted, its rdb$db_key will be reused. When a database is backed up with gbak and restored, gbak creates a new empty database and stores all the backed up record into it. Unless the database is very stable, records will be stored in different places, resulting in different rdb$db_key values.

In short, do not store rdb$db_key values in your database!

> This fails with error "malformed string", it looks like either rdb$db_key does not match char(8)
>

Right. The rdb$db_key is eight bytes of binary some of which will not be valid characters in any normal character set. If you want to use the rdb$db_key as a short-term locator value, use character set Octets which is value agnostic.

Good luck,

Ann

>
>
>
>