A rowid value is a 64 bit integer.
If a table has a column that is defined to be an
integer primary key, this column stores the rowid, that is, the respective column names is an alias for
rowid (or vice versa).
Demonstration
The following script tries to demonstrate rowids:
.mode column
.width 2 2 2 2 10
create table tq84_foo (
id abcdef primary key,
col_1 text
);
insert into tq84_foo values ( 2, 'two' );
insert into tq84_foo values ( 7, 'seven' );
insert into tq84_foo values ( 5, 'five' );
insert into tq84_foo values ( 9, 'three' );
update tq84_foo set id = 3 where col_1 = 'three';
select
rowid,
oid, -- alternative name for rowid
_rowid_, -- alternative name for rowid
id,
col_1
from
tq84_foo;
--
-- 1 1 1 2 two
-- 2 2 2 7 seven
-- 3 3 3 5 five
-- 4 4 4 3 three
drop table tq84_foo;
--
-- If the primary key of the table is one column of type »integer«, it
-- becomes an alias for the rowid:
create table tq84_foo (
id integer primary key,
col_1 text
);
insert into tq84_foo values ( 2, 'two' );
insert into tq84_foo values ( 7, 'seven' );
insert into tq84_foo values ( 5, 'five' );
insert into tq84_foo values ( 9, 'three' );
update tq84_foo set id = 3 where col_1 = 'three';
select
rowid,
oid,
_rowid_,
id,
col_1
from
tq84_foo;
--
-- 2 2 2 2 two
-- 3 3 3 3 three
-- 5 5 5 5 five
-- 7 7 7 7 seven
drop table tq84_foo;
Selecting the last inserted rowid
The rowid of the last inserted row can be selected with the last_insert_rowid() function:
create table ri (col_1, col_2);
insert into ri values (42, 'forty-two');
select last_insert_rowid();
-- 1
insert into ri (rowid, col_1, col_2) values (17, 99, 'ninety-nine');
select last_insert_rowid();
-- 17