Is there an auto increment in sqlite?
SqlSqliteCygwinSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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).
Solution 2 - Sql
Yes, this is possible. According to the SQLite FAQ:
> A column declared INTEGER PRIMARY KEY
will autoincrement.
Solution 3 - Sql
As of today — June 2018
Here is what official SQLite documentation has to say on the subject (bold & italic are mine):
> 1. 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. > > 2. 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. > > 3. 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. > > 4. 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.
Solution 4 - Sql
Have you read this? How do I create an AUTOINCREMENT field.
INSERT INTO people
VALUES (NULL, "John", "Smith");
Solution 5 - Sql
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
Solution 6 - Sql
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
Solution 7 - Sql
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.
Solution 8 - Sql
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.
Solution 9 - Sql
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