Menu
Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done?
ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server.
261
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment. The keyword AUTOINCREMENT can be used with INTEGER field only.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
If you don't specify the rowid value or you use a NULL value when you insert a new row, SQLite automatically assigns the next sequential integer, which is one larger than the largest rowid in the table. The rowid value starts at 1.
The sequence functionality is implemented using SQLite function plugins, as such it is necessary to use the 'select' keyword as a prefix to all sequence APIs. The SQL API sequence support is a partial implementation of the sequence API defined in the SQL 2003 specification.
DELETE
FROM MyTableName
select *
from SQLITE_SEQUENCE
update SQLITE_SEQUENCE
set seq = 0
where name ='MyTableName'
In SQLite there is a table named SQLITE_SEQUENCE, which tracks the largest RowId value that a table has. You can do insert, updates and deletes on this table. For example, to mimic similar functionality as the TRUNCATE TABLE statement SQL Server you could something like:
DELETE FROM MyTableName;
DELETE FROM SQLITE_SEQUENCE WHERE NAME = 'MyTableName';
In the above example all data from MyTableName is removed, and the auto increment rowid is reset by removing the value from the SQLITE_SEQUENCE table. See the documentation for AUTOINCREMENT for more information.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
