Is there an auto increase in sqlite?

Question

I am trying to create a table with auto-incrementing primary key in Sqlite3. I'm not sure if this is really possible, but I hope only to assign 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 the value, I only hoped to do:

 INSERT INTO people VALUES ("John", "Smith"); 

Is it possible?

I am running sqlite3 under cygwin on Windows 7.

Answer 1

You get one free, called ROWID. It is in every SQLite table whether you ask about it or not.

If you include a column of type INTEGER PRIMARY KEY, this column points to (is an alias) for the automatic ROWID column.

A ROWID (by whatever name you call) is assigned a value whenever you insert a row, as you would expect. If you explicitly assign a NULL value to INSERT, it will receive that specified value instead of automatically incrementing. If you explicitly assign a NULL value to INSERT, it will receive the next auto-increment value.

In addition, you should avoid:

  INSERT INTO people VALUES ("John", "Smith"); 

and use

  INSERT INTO people (first_name, last_name) VALUES ("John", "Smith"); 

instead of this. The first version is very fragile - if you ever added, moved or deleted columns in the table definition, INSERT either returned or returned incorrect data (with values ​​in the wrong columns).

Answer 2

Yes it is possible. According to SQLite docs, "The column declared by INTEGER PRIMARY KEY will be auto-increment." ( http://www.sqlite.org/autoinc.html )

Answer 3

Have you read this? How to create an AUTOINCREMENT field.

 INSERT INTO people VALUES (NULL, "John", "Smith"); 

Answer 4

SQLite AUTOINCREMENT is a keyword used to automatically increment a field in a table. We can automatically increase the value of a field using the AUTOINCREMENT keyword when creating a table with a specific column name to automatically increase it.

The AUTOINCREMENT keyword can only be used with the INTEGER field. Syntax:

The main use of the AUTOINCREMENT keyword is as follows:

CREATE TABLE table_name (column1 INTEGER AUTOINCREMENT, column2 data type, column3, ..... columnN data type,);

See the example below: Consider the COMPANY table, which will be created as follows:

sqlite> CREATE TABLE TB_COMPANY_INFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, CHAR (50), SALARY REAL);

Now paste the following entries into table TB_COMPANY_INFO:

INSERT INTO TB_COMPANY_INFO (NAME, AGE, ADDRESS, DISPLAY) VALUES ('MANOJ KUMAR', 40, 'Meerut, UP, INDIA', 200000.00);

Now select the entry SELECT * FROM TB_COMPANY_INFO TITLE ID NAME ADDRESS 1 Manoi Kumar 40 Meerut, UP, INDIA 200000.00

Answer 5

You cannot specify the AUTOINCREMENT keyword next to PRIMARY KEY . An example of creating a primary auto-increment key and insert:

 $ 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 

Answer 6

Besides rowid, you can define your own auto-increment field, but this is not recommended. This is always the best solution when we use rowid, which automatically increases.

"The AUTOINCREMENT keyword overlaps additional CPU resources, memory, disk space, and disk I / O overhead and should be avoided unless strictly required. Usually this is not required."

Read more here .

Answer 7

I know this answer was a bit late.
My goal for this answer is for all links if they run into this type of problem with SQLite now or in the future and it is hard for them to handle it.

Now, looking back at your request, there 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. In this way,

Just in case, when you work with SQLite, I suggest you check out DB Browser for SQLite . It also works on different platforms.