On 15/11/2016 19:58, 'Some One' [email protected] [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. Helen
