CODEWITHSUNDEEP
iossqliphonesqlite
oarfish
oarfish

Reputation: 4631

sqlite: Cannot select id while rowid works

I have a database schema like this

CREATE TABLE IF NOT EXISTS 
    rephotos (id integer AUTO_INCREMENT, 
              beforePath text, 
              beforeThumbnail blob, 
              afterPath text, 
              afterThumbnail blob, 
              PRIMARY KEY(id)
              );

When trying to SELECT the ids in the database like this

SELECT id FROM rephotos;

it prints nothing. However if I use

SELECT rowid FROM rephotos;

it works as expected. The reason this confuses me is that the sqlite documentation specifically states that

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 described above 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.

What am I doing wrong?

Upvotes: 1

Views: 335

Answers (1)

CL.
CL.

Reputation: 180161

The documentation says:

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.

AUTOINCREMENT is spelled wrong, so the column type is not exactly "INTEGER" but "INTEGER AUTO_INCREMENT".

Upvotes: 1

Related Questions