Why Can't You Use Sqlite Rowid as a Primary Key

Why can't you use SQLite ROWID as a Primary key?

Summary from SQLite.org:

In SQLite, table rows normally have a 64-bit signed integer ROWID
which is unique among all rows in the same table. (WITHOUT ROWID
tables are the exception.)

If a table contains a column of type INTEGER PRIMARY KEY, then that
column becomes an alias for the ROWID. You can then access the ROWID
using any of four different names, the original three names (ROWID,
_ROWID_, or OID) or the name given to the INTEGER PRIMARY KEY
column. All these names are aliases for one another and work equally
well in any context.

Just use it as the primary key.

INTEGER PRIMARY KEY vs rowid in SQLite

It would appear that there is an overhead of having an alias for the rowid of a byte (I think) per row, which I believe is explained by :-

When an SQL table includes an INTEGER PRIMARY KEY column (which
aliases the rowid) then that column appears in the record as a NULL
value. SQLite will always use the table b-tree key rather than the
NULL value when referencing the INTEGER PRIMARY KEY column.
Database File Format - 2.3. Representation Of SQL Tables.

The 1 byte per row appears to be pretty close according to the following testing:-

Two databases were created with the two differing tables, loaded with 1,000,000 million rows using the following SQL :-

For the First :-

DROP TABLE IF EXISTS points;
CREATE TABLE IF NOT EXISTS points (tags BLOB NOT NULL, lon INTEGER NOT NULL, lat INTEGER NOT NULL);
WITH RECURSIVE counter(tags,lon,lat) AS (SELECT x'00000000', 0,0 UNION ALL SELECT tags, random() AS lon, random() AS lat FROM counter LIMIT 1000000)
INSERT INTO points (tags,lon,lat) SELECT * FROM counter;
SELECT * FROM points;
VACUUM

For the Second (with an alias of the rowid):-

DROP TABLE IF EXISTS points;
CREATE TABLE IF NOT EXISTS points (id INTEGER PRIMARY KEY, tags BLOB NOT NULL, lon INTEGER NOT NULL, lat INTEGER NOT NULL);
WITH RECURSIVE counter(tags,lon,lat) AS (SELECT x'00000000', 0,0 UNION ALL SELECT tags, random() AS lon, random() AS lat FROM counter LIMIT 1000000)
INSERT INTO points (tags,lon,lat) SELECT * FROM counter;
SELECT * FROM points;
VACUUM

The the resultant file sizes were 29484Kb and 30600Kb respectively.

That being a difference of 30600 - 29484 = 1,116, multiply this by 1024 = 1142784 (not that far off the 1,000,000 rows, pages and freespace probably accounting for the discrepancy ).

• Note the VACUUM command made no difference (as they were new tables there was no expectation that they would.)

Behavior of SqlLite rowid in case of a INTEGER primary key

An INTEGER PRIMARY KEY column is just an alias for the rowid. It acts the same (Having a value automatically assigned if left out when inserting a row), and doesn't even take up any extra space in the database. You can reference the column via its name, rowid, or any of the other standard aliases for rowid like oid.

From the documentation:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

If you just do

INSERT INTO t2(name) VALUES ('max');

a value will be automatically generated for xid instead of explicitly using the one provided in the insert like in your example.

In Android Sqlite is ROWID same as primary Key?

All tables (ignoring exceptions) have a row ID.

When there is no column declared as INTEGER PRIMARY KEY, the rowid is separate from the other columns:

CREATE TABLE A ( _id INT PRIMARY KEY, name TEXT ); -- not "INTEGER"

| rowid | _id | name |
+-------+-----+------+
|     1 |   1 | this |
|     2 |   2 | that |
|     3 |  30 | NULL |  -- does not need to be the same

If there is an INTEGER PRIMARY KEY column, both rowid and the declared column name are aliases for the same value:

CREATE TABLE B ( _id INTEGER PRIMARY KEY, name TEXT );

| rowid | name |
| = _id |      | 
+-------+------+
|     1 | this |
|     2 | that |
|     3 | NULL |

Pros/cons of relying on rowid instead of _id primary key (SQLite)

Many parts of the Android framework expect your queries to return a unique integer column called _id.

If the contents of the mapping table never show up in such an Android component (e.g., looking up both names and showing the name1/name2 mappings in a list view), then you don't need it.

However, (almost) all tables have the internal rowid, so it would not hurt to declare it explicitly as _id, especially if you do not know if you might ever need it.

Referring to it as rowid is not always a good idea because without an explicit column, its value might change.

---

Related Topics