Search code examples
sqliteprimary-keyauto-increment

Is there 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.

Solution

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