Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 3 of 3 total |
Problem with generated field after Alter Column |
Tue, Sep 3 2013 10:17 PM | Permanent Link |
Peter Evans | 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 |
Wed, Sep 4 2013 5:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 4 2013 9:04 PM | Permanent Link |
Peter Evans | 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 |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |