Is there an auto increment in sqlite?

sql sqlite cygwin
ewok picture ewok · Oct 26, 2011 · Viewed 150.5k times · Source

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

Larry Lustig picture Larry Lustig · Oct 26, 2011

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More