SQLite | Tables | ROWID and INTEGER PRIMARY KEY

Tags:

SQLite automatically assigns a ROWID to each row. This article explains how to use it and how it relates to INTEGER PRIMARY KEY.

Querying ROWID

ROWID is a hidden table column populated whenever a row is inserted.

create table user (id integer, name text);
insert into user values (3, 'devkuma');
insert into user values (8, 'araikuma');
select *, rowid from user;

id          name        rowid
3           devkuma     1
8           araikuma    2

For a new row, SQLite normally assigns one more than the largest current ROWID:

insert into user values (5, 'kimkc');

The new row receives ROWID 3. ROWID can also be used in a WHERE condition:

select *, rowid from user where rowid = 2;

For compatibility, SQLite also supports the aliases OID and _ROWID_:

select *, rowid, oid, _rowid_ from user;

Setting ROWID

Although assigned automatically, ROWID can be provided explicitly:

insert into user (id, name, ROWID) values (10, 'happykuma', 8);
select *, rowid from user;

3|devkuma|1
8|araikuma|2
5|kimkc|3
10|happykuma|8

ROWID values must be unique. Reusing an existing value fails:

insert into user (id, name, ROWID) values (9, 'mykuma', 3);
-- Error: UNIQUE constraint failed: user.rowid

Relationship between ROWID and INTEGER PRIMARY KEY

A column declared INTEGER PRIMARY KEY is an alias for the table’s ROWID. Assigning either one also assigns the other.

create table user (id integer primary key, name text);
insert into user values (1, 'devkuma');
insert into user values (6, 'kimkc');
insert into user values (3, 'ariakuma');
select *, rowid from user;

id          name        id
1           devkuma     1
3           ariakuma    3
6           kimkc       6

The selected ROWID is displayed with the primary-key column name because id is its alias.