SQLite two rowid's created when I explicitly create one

Question

I have a sqlite table and I need to keep the ids from changing when I VACUUM the database. The documentation says that VACUUM will not change the rowids of a table that has an explicit INTEGER PRIMARY KEY.

So, I created a table with

CREATE TABLE tableName (
  "rowid" INTEGER PRIMARY KEY, 
  "updated" DATETIME DEFAULT (CURRENT_TIMESTAMP),
  "description" TEXT
)

But that makes a table with two rowid columns. In SQLite Manager add-on for Firefox I see both, and when I try to access the result set in Java it says "ambiguous column: 'rowid'". Is there a way to explicitly create rowid or do I have to use a different name?

Luke Willis Luke Willis · Accepted Answer · 2013-08-26T21:37:03

rowid is a column of every SQLite table by default

If a 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.

Thus, your rowid column doesn't replace the default rowid. Your key is an alias for the default rowid.

Because of this, you really shouldn't name your column rowid, as it is already used, and will result in "ambiguous column" errors, as you have experienced already.

The documentation is saying that the rowid that SQLite is generating will not be changed if your table contains some INTEGER PRIMARY KEY. The PK does not need to be the rowid.

SQLite two rowid's created when I explicitly create one

1 Answers