I have a table that has a column used as a primary key that is set to Generate as Identity and By Default Only.

It appears that this isn't working correctly as I'm getting duplicate primary key errors when I try to add records.  It looks like the value is not being incremented.  I can't find anything in the documentation but I thought the purpose of By Default Only would increment the value unless it was provided.  Or am I completely wrong?

Elevate DB 2.21 Build 1.

= Steve

On 2/10/2016 5:33 PM, Steve Gill wrote:
> I have a table that has a column used as a primary key that is set to Generate as Identity and By Default Only.

I assume it's Integer based ?

> It appears that this isn't working correctly as I'm getting duplicate primary key errors when I try to add records.  It looks like the value is not being incremented.  I can't find anything in the documentation but I thought the purpose of By Default Only would increment the value unless it was provided.  Or am I completely wrong?
> Elevate DB 2.21 Build 1.

yes - "By Default Only" will only generate it if you do not specify it
yourself when inserting data.

I just did a quick test in EDBManager and it seems to work fine here
(2.21 build 1). I have a integer column generated by default and primary
key constraint on the same column.
When i leave it blank it creates the next logical entry (starting from 1
and incrementing). If i do specify value myself it accepts it and next
time auto generates next logical one - i.e. if i have values 1..5 and
skip to 20 with my entry then next generated one is 21 etc.

Can you provide more info on how this is happening exactly : your table
SQL and operations order you're doing when running into key violation ?

Any chance your code is populating a duplicate value and getting key
violation ?

Raul

Hi Raul,

<< Can you provide more info on how this is happening exactly : your table
SQL and operations order you're doing when running into key violation ?

Any chance your code is populating a duplicate value and getting key
violation ? >>

Yes, they are integer based.

I'm allowing users to migrate data from a DBISAM version of the program into an EDB version.  As the primary keys need to be the same for all of the data to match up between tables, I set it to By Default Only in the relevant tables so the existing IDs can be used.

That all works fine and there are no duplicate values. What's happening is, if I then try to add records from within the application I get the error.  On investigation I saw that in all of the tables the seed value is still at 0 even though there are hundreds of records.  This is why I'm getting duplicate key errors.

What I've done now to get around it is, once all of the data has been imported, I get the MAX key value for each table, and then alter each table and make the seed value the MAX key value +1.  That seems to have fixed the problem.

So the issue seems to be that, after adding all of these records and setting the primary key value in code, the actual seed value hasn't changed, ie. it remains at 0.

To explain it another way, if you set the key value yourself when adding records, the seed value is not incremented.  It's only when the table itself adds the keys itself that it gets incremented.

I hope that makes sense.

= Steve

Steve,

<< I'm allowing users to migrate data from a DBISAM version of the program into an EDB version. >>

Are you using an EDB migrator to do this, or your own code ?  The EDB migrator should handle setting the seed value, as necessary, for you.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

<< Are you using an EDB migrator to do this, or your own code ?  The EDB migrator should handle setting the seed value, as necessary, for you.  >>

I'm using my own code as the EDB database is quite different from the DBISAM one, ie. there are different tables with different columns, etc.

Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records?

= Steve

Steve


>Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records?

Yes

When I did my last conversion I chose to handle things in a different way - For the master tables I built a cross reference list in DBISAM - old ID & new ID allowing ElevateDB to create the new ID. Left the foreign keys as was in the detail tables and then after they'd all been transfered went through converting old fk to new fk.

Part of the reason for doing it that way was just to clean up a system that had been in use for many years and had duff foreign keys, gaps etc. It ended up adding more work but at least I started clean - its probably back into the crapped up state now though

Its so long back that I don't even have the code on a machine any more - may be on an archive somewhere.

Roy Lambert

Steve,

> Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records?

Correct.

Here a code snippet from one of my procedures to do that automatically:

PREPARE Stmt FROM
         'SELECT TableName, Name from information.TableColumns where
type = ''integer'' and Identity';
      OPEN Result;

      FETCH FIRST FROM Result('TableName','Name') INTO
FTableName,FColumnName;
      WHILE NOT EOF(Result) DO

         Execute Immediate 'SELECT coalesce(max(' + FColumnName + '),0)
INTO ?
                            from ' + FTableName + ' where ' +
FColumnName + ' between ? and ?'
                            using LastAutoIncValue, RangeFrom, RangeTo-1;

         IF LastAutoIncValue between RangeFrom and RangeTo THEN
            SET NewStartValue = LastAutoIncValue + 1;
         ELSE
            SET NewStartValue = RangeFrom;
         END IF;


         Execute Immediate 'ALTER TABLE "' + FTableName + '" ALTER
COLUMN "' + FColumnName +
                           '" RESTART with ' + CAST(NewStartValue as
VARCHAR);
         SET LOG MESSAGE TO FTableName + ' - ' + FColumnName + ': ' +
CAST(NewStartValue as VARCHAR);

         FETCH NEXT FROM Result('TableName','Name') INTO
FTableName,FColumnName;
      END WHILE;

Regars Uli

Thanks Roy and Uli,

I have already fixed it by setting new seed values after all of the records were imported.  It would be nice if there was more documentation on this somewhere.  I couldn't find it anywhere in the manuals.

= Steve

Steve,

<< I have already fixed it by setting new seed values after all of the records were imported.  It would be nice if there was more documentation on this somewhere.  I couldn't find it anywhere in the manuals. >>

It's in the FAQ:

http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=altering_identity_seed_value

Tim Young
Elevate Software
www.elevatesoft.com

<< It's in the FAQ:>>

Thanks Tim. I didn't even know the FAQs were there.

= Steve