How to insert a unique ID into each SQLite row?

Question

I have the following SQLite code. How do I insert an auto generating unique ID into each row?

    tx.executeSql('DROP TABLE IF EXISTS ENTRIES');
    tx.executeSql('CREATE TABLE IF NOT EXISTS ENTRIES (id unique, data)');

    tx.executeSql('INSERT INTO ENTRIES (id, data) VALUES (1, "First row")');
    tx.executeSql('INSERT INTO ENTRIES (id, data) VALUES (2, "Second row")');

score 47 · Accepted Answer · edited May 01 '23 at 20:48

47

You could define id as an auto increment column:

create table entries (id integer primary key autoincrement, data)

As MichaelDorner notes, the SQLite documentation says that an integer primary key does almost the same thing and is slightly faster. A column of that type is an alias for ROWID, which behaves like an autoincrement column with the difference that without AUTOINCREMENT the ID of the row might be reused. "In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows."[source]

create table entries (id integer primary key, data)

This behavior is implicit and could catch inexperienced SQLite developers off-guard.

edited May 01 '23 at 20:48

Mehrad Mahmoudian

3,466
32
36

answered Feb 18 '12 at 15:03

Andomar

232,371
49
380
404

3

Notice, that "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." ( https://www.sqlite.org/autoinc.html ) – Michael Dorner Apr 16 '15 at 09:42
@MichaelDorner: Thanks, interesting, I've updated the answer. – Andomar Apr 16 '15 at 12:00
4

Note that it *has* to be `integer` and not `int` in PHP with PDO, otherwise it doesn't automatically set the id. – starbeamrainbowlabs Aug 27 '15 at 13:18
1

if you have a bunch of distinct strings that you want to store and if autoincrement has to be avoided, how would you go about it? considering that these strings will be repeated in a different time many times (foreign key situation) – PirateApp Apr 19 '18 at 16:43
1

The decision about using AUTOINCREMENT or not should be whether or not you want keys in a given table being reused. For instance if row with key=7 has been deleted, would you like to insert the next row using the (now available) 7 as a key? If you don't want to reuse it, then you need to go with AUTOINCREMENT – martom Jun 03 '18 at 21:33

score 21 · Answer 2 · answered Feb 18 '12 at 15:06

This is the syntax that I use.

 id INTEGER PRIMARY KEY AUTOINCREMENT,

Simply don't provide data for the autoincrement column

 tx.executeSql('INSERT INTO ENTRIES (id, data) VALUES (NULL, "First row")');

Or even simpler

 tx.executeSql('INSERT INTO ENTRIES (data) VALUES ("First row")');

score 4 · Answer 3 · answered Feb 18 '12 at 15:03

autoincrement is your friend buddy.

CREATE TABLE IF NOT EXISTS ENTRIES (id integer primary key autoincrement, data);
INSERT INTO ENTRIES (data) VALUES ("First row");
INSERT INTO ENTRIES (data) VALUES ("Second row");

and then:

> SELECT * FROM ENTRIES;
1|First row
2|Second row

score 1 · Answer 4 · answered Jul 16 '13 at 10:23

1

for the INSERT, better provide a "null" value for the corresponding autoincrement value's question mark placeholder.

 tx.executeSql('INSERT INTO ENTRIES (id, data) VALUES (?, ?)', [null, "First row"]);

answered Jul 16 '13 at 10:23

netalex

437
5
15

score 1 · Answer 5 · answered Jul 28 '17 at 11:31

1

This worked perfectly for me

c.execute('INSERT INTO WEBSITES (url) VALUES (?)', [url])

answered Jul 28 '17 at 11:31

oriolowonancy

283
3
9

How to insert a unique ID into each SQLite row?

5 Answers5

Linked

Related