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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Yes  this is possible   According to the SQLite FAQ      A column declared INTEGER PRIMARY KEY will autoincrement

User · Answer

As of today     June 2018    Here is what official SQLite documentation has to say on the subject  bold  amp  italic are mine          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    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    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    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

User · Answer

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

User · Answer

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

User · Answer

Have you read this  How do I create an AUTOINCREMENT field   INSERT INTO people VALUES  NULL   John    Smith

User · Answer

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

[sql] Is there an auto increment in sqlite?

Examples related to sql

Examples related to sqlite

Examples related to cygwin