[sql] Is there an auto increment in sqlite?

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.

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.

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

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.

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

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

A column declared INTEGER PRIMARY KEY will autoincrement.

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.

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

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

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