Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set start value for AUTOINCREMENT in SQLite

Tags:

sqlite

How can I set the start value for an AUTOINCREMENT field in SQLite?

like image 896
Christian Davén Avatar asked Mar 28 '09 14:03

Christian Davén


People also ask

How do I set Autoincrement value?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.

How do I Autoincrement in SQLite?

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.

When you insert a new row into a SQLite database it automatically generates a value for?

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.

Is Autoincrement primary key?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.


1 Answers

From the SQLite web site:

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

I tried this, and it works:

UPDATE SQLITE_SEQUENCE SET seq = <n> WHERE name = '<table>' 

Where n+1 is the next ROWID you want and table is the table name.

like image 113
Christian Davén Avatar answered Sep 20 '22 11:09

Christian Davén