Subject : Problem with generated field after Alter Column

Firstly I create a table with the CREATE TABLE SQL.

The field in question is defined as :-

      '"RecNum" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1), ' +

Note that the field RecNum is not indexed.

This seems to work as when I add the first record RecNum = 1. The second
record is RecNum = 2.

The problem occurs after I execute the following SQL :-

    Execute(
      'ALTER TABLE "Source" ' +
      'ALTER COLUMN "RecNum" AS ' +
        'INTEGER GENERATED ALWAYS AS IDENTITY ' +
        '(START WITH ' + IntToStr(LastAutoInc) +  ', INCREMENT BY 1) '
            );

I pass to this SQL the value LastAutoInc = 999.

What I expect to see is that the next inserted record is :-

    RecNum = 1000

But I don't. I will see

    RecNum = 3

If I exit my program and fire up ElevateDB Manager Unicode, and look at
the field RecNum, I see Seed = 999. Well I am not seeing Seed = 0, so I
think that is OK?

Is there something else I need to do to Alter the Column RecNum?

Regards,
  Peter Evans

Peter


This was an issue way back. I've never used it but there is a RESTART WITH clause eg

ALTER TABLE "MyTable" ALTER COLUMN ID RESTART WITH 1

This was from a post of Tim's

------------------------------------------------------------------------------------------------------------------

Seed value in the catalog <> Next seed value in the table file

The first controls what seed value is used for the IDENTITY column when a
new table file is created on disk, and the second controls which value is
assigned next for the IDENTITY column.  In the case of a newly-created
table, the two are always the same.  For existing tables, the two can be
completely different.  For example, in your case you will want to use the
RESTART WITH clause to set the next IDENTITY column value for the existing
table files.  However, you *don't'* want EDB to create a new table file that
starts with a seed value that matches that value - you would want the
catalog to continue to have the seed value set to 0 for any newly-created
table files.

------------------------------------------------------------------------------------------------------------------

Roy Lambert

On 4/09/2013 7:24 PM, Roy Lambert wrote:
> Peter
>
>
> This was an issue way back. I've never used it but there is a RESTART WITH clause eg
>
> ALTER TABLE "MyTable" ALTER COLUMN ID RESTART WITH 1
>

Roy,
  I followed that advice as per the following :-

    Execute(
      'ALTER TABLE "Source" ' +
      'ALTER COLUMN "RecNum" ' +
        'RESTART WITH ' + IntToStr(LastAutoInc)
            );

I am very pleased to say that this solution is working.

Regards,
  Peter Evans