Is ROWID in SQLite automatically set?

Issue

So, I have this application on Ionic that uses the SQLite ngCordova plugin for internal storage. On it, I create a table with the following command:

db.executeSql(
    "CREATE TABLE IF NOT EXISTS Scans_table (" +
      //"id         TEXT PRIMARY KEY  NOT NULL," +
      "name       TEXT              NOT NULL," +
      "comment    TEXT, " +
      "text       TEXT              NOT NULL, " +
      "format     TEXT              NOT NULL, " +
      "dateTaken  TEXT              NOT NULL, " +
      "imgSource  TEXT              NOT NULL)", ... );

According to this, if I don't have a column that is a primary key and an integer, a ROWID should be set as a unique identifier.

The problem is, when I query the table with a simple SELECT * ... I see all the rows that I set, and not the ROWID.

Is there a different way to set/check the ROWID?

Solution

As shown in the documentation, your table does have an internal rowid column. However, if your table's column list does not include an alias for this column (i.e., an INTEGER PRIMARY KEY column), the rowid column is not included in SELECT *.

You can just add the column in the SELECT clause:

SELECT rowid, * FROM Scans_table ...

However, if you actually use the rowid, it is a better idea to have an explicit INTEGER PRIMARY KEY column. This not only documents the table structure better, but also prevents the rowid values from being changed by the VACUUM statement.

Answered By - CL.