Is there AUTO INCREMENT in SQLite?

by

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

More Related Contents:

  1. Auto Increment after delete in MySQL
  2. SQLite auto-increment non-primary key field
  3. SQL – Inserting a row and returning primary key
  4. Select first row in each GROUP BY group?
  5. SQLite – UPSERT *not* INSERT or REPLACE
  6. How can I list the tables in a SQLite database file that was opened with ATTACH?
  7. Update table values from another table with the same user name
  8. SQL Select between dates
  9. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  10. Foreign key referring to primary keys across multiple tables?
  11. How can I define a composite primary key in SQL?
  12. FULL OUTER JOIN with SQLite
  13. SQLite – ORDER BY RAND()
  14. Select random row(s) in SQLite
  15. sqlite alter table add MULTIPLE columns in a single statement
  16. Drop all tables command
  17. How to do IF NOT EXISTS in SQLite
  18. Core Data Performance with Single Parent Entity
  19. How to create timestamp column with default value ‘now’?
  20. String concatenation does not work in SQLite
  21. Unfindable SQLite Syntax Error
  22. How to select the first N rows of each group?
  23. adding primary key to sql view
  24. SQL error: misuse of aggregate
  25. SQLite query, ‘LIKE’
  26. Store and reuse value returned by INSERT … RETURNING
  27. How to use a calculated column by another calculated column
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment