VistaDB Support Center

Start a new topic

How get 1 back to ID column?

Now, I have a table, tTest at a VistaDB database. Only 2 columns for it, ID and Name. ID is the primary key, and its seed is 1 and will  automatically increase by 1 when a new record is inserted. The test data is: 

ID  Name

1    A

2    B

3    C

After deleting all rows, I would like to insert another row "1  X". But I can only get "4  X". How to get "1  X"? Thanks a lot


You could try (tested under V5.0.9.1345)


 

ALTER TABLE ttest ALTER COLUMN ID INT NOT NULL IDENTITY (1, 1);

 


Also, if you're using Data Builder you can use the Alter Column GUI to edit the Identity settings.


And there is also an API call IVistaDBTable.CreateIndex(...) to do it from .NET code.

You can try in SQL:

SET IDENTITY_INSERT table_name ON

then do your insert

and then

SET IDENTITY_INSERT table_name OFF

IDENTITY_INSERT is for temporarily turning off Identity in a given table to allow a value to be specified for that column in an INSERT (otherwise, SQL Server would give an error, and VistaDB has historically just ignored the specified value--except, now, if you use "Strict" compatibility mode VistaDB will throw an error), but it would not reset the seed for when IDENTITY_INSERT is turned back off and Identity resumes.  So, it's good to know about for that slightly different use case.


Unfortunately, it's not currently supported in VistaDB.  However, it is one of several modern T-SQL language features we are looking to add support for in the near future, probably as part of 5.2 later this year.

Login to post a comment