sql - Is there an 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).