Is there AUTO INCREMENT in SQLite?

Question

I am trying to create a table with an auto-incrementing primary key in Sqlite3. I am not sure if this is really possible, but I am hoping to only have to designate the other fields.

For example:

CREATE TABLE people (
  id integer primary key auto increment, 
  first_name varchar(20), 
  last_name varchar(20)
);

Then, when I add a value, I was hoping to only have to do:

INSERT INTO people VALUES ("John", "Smith");

Is this even possible?

I am running sqlite3 under cygwin in Windows 7.

Answer 1

You get one for free, called ROWID. This is in every SQLite table whether you ask for it or not.

If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.

ROWID (by whatever name you call it) is assigned a value whenever you INSERT a row, as you would expect. If you explicitly assign a non-NULL value on INSERT, it will get that specified value instead of the auto-increment. If you explicitly assign a value of NULL on INSERT, it will get the next auto-increment value.

Also, you should try to avoid:

 INSERT INTO people VALUES ("John", "Smith");

and use

 INSERT INTO people (first_name, last_name) VALUES ("John", "Smith");

instead. The first version is very fragile — if you ever add, move, or delete columns in your table definition the INSERT will either fail or produce incorrect data (with the values in the wrong columns).

Answer 2

Yes, this is possible. According to the SQLite FAQ:

A column declared INTEGER PRIMARY KEY will autoincrement.

You then have to pass NULL on that column when inserting. Example:

sqlite3 tmp.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 tmp.sqlite 'insert into t values (NULL, 10, -10), (NULL, 20, -20)'
sqlite3 tmp.sqlite 'select * from t'

outputs:

1|10|-10
2|20|-20

Tested on Ubuntu 23.04, sqlite 3.40.1.

Answer 3

As of today — June 2018

---

Here is what official SQLite documentation has to say on the subject (bold & italic are mine):

1. 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.

2. 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.

3. 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 one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

4. 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.

Answer 4

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 incrementing it.

The keyword AUTOINCREMENT can be used with INTEGER field only. Syntax:

The basic usage of AUTOINCREMENT keyword is as follows:

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

For Example See Below: Consider COMPANY table to be created as follows:

sqlite> CREATE TABLE TB_COMPANY_INFO(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Now, insert following records into table TB_COMPANY_INFO:

INSERT INTO TB_COMPANY_INFO (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'MANOJ KUMAR', 40, 'Meerut,UP,INDIA', 200000.00 );

Now Select the record

SELECT *FROM TB_COMPANY_INFO
ID      NAME            AGE     ADDRESS             SALARY
1       Manoj Kumar     40      Meerut,UP,INDIA     200000.00

Answer 5

Have you read this? How do I create an AUTOINCREMENT field.

INSERT INTO people
VALUES (NULL, "John", "Smith");

Always insert NULL as the id.

Answer 6

One should not specify AUTOINCREMENT keyword near PRIMARY KEY. Example of creating autoincrement primary key and inserting:

$ sqlite3 ex1

CREATE TABLE IF NOT EXISTS room(room_id INTEGER PRIMARY KEY, name VARCHAR(25) NOT NULL, home_id VARCHAR(25) NOT NULL);

INSERT INTO room(name, home_id) VALUES ('test', 'home id test');

INSERT INTO room(name, home_id) VALUES ('test 2', 'home id test 2');

SELECT * FROM room;

will give:

1|test|home id test
2|test 2|home id test 2

Answer 7

Beside rowid, you can define your own auto increment field but it is not recommended. It is always be better solution when we use rowid that is automatically increased.

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.

Read here for detailed information.

Answer 8

What you do is correct, but the correct syntax for 'auto increment' should be without space:

CREATE TABLE people (id integer primary key autoincrement, first_name string, last_name string);

(Please also note that I changed your varchars to strings. That's because SQLite internally transforms a varchar into a string, so why bother?)

then your insert should be, in SQL language as standard as possible:

INSERT INTO people(id, first_name, last_name) VALUES (null, 'john', 'doe');

while it is true that if you omit id it will automatically incremented and assigned, I personally prefer not to rely on automatic mechanisms which could change in the future.

A note on autoincrement: although, as many pointed out, it is not recommended by SQLite people, I do not like the automatic reuse of ids of deleted records if autoincrement is not used. In other words, I like that the id of a deleted record will never, ever appear again.

HTH

Answer 9

I know this answer is a bit late.
My purpose for this answer is for everyone's reference should they encounter this type of challenge with SQLite now or in the future and they're having a hard time with it.

Now, looking back at your query, it should be something like this.

CREATE TABLE people (id integer primary key autoincrement, first_name varchar(20), last_name varchar(20));

It works on my end. Like so,

Just in case you are working with SQLite, I suggest for you to check out DB Browser for SQLite. Works on different platforms as well.

Answer 10

In SQLite, you can auto-increment with and without AUTOINCREMENT as shown below. *AUTOINCREMENT must be used with INTEGER PRIMARY KEY otherwise there is error:

CREATE TABLE person (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT           -- ↑↑↑↑↑↑↑↑↑↑↑↑↑
);

CREATE TABLE person (
  id INTEGER PRIMARY KEY, -- No AUTOINCREMENT
  name TEXT
);

And, AUTOINCREMENT can avoid to reuse the deleted numbers unless explicitly specifying them while no AUTOINCREMENT cannot according to the doc. *My question and my answer explain the difference between AUTOINCREMENT and no AUTOINCREMENT.