How can I use an autoincrement variable in sqlite?

Question

this is my code in sqlite: stmt.execute("create table if not exists people (name text,ID INTEGER PRIMARY KEY AUTOINCREMENT)");

but when I see my table in Navicat and when I want to use it all of it is null. I want to use it like an integer (1,2,...). What should I do? please help me...

Maybe instead of "not exists" try drop table before create. It sounds like you already have an invalid table because it is impossible to have a table with a null-value primary key — avk
– avk, Commented Mar 9, 2015 at 19:00
thanks...I tried deleting db file and create it one more time and it is fixed now. — dashsiamak
– dashsiamak, Commented Mar 9, 2015 at 19:50

SSC · Accepted Answer · 2015-03-11 00:49:19Z

A simple google query turned out with this result:

Summary

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually the one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

Reference: AutoIncrement SQLite

Collectives™ on Stack Overflow

How can I use an autoincrement variable in sqlite?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related