19

This does not execute:

create table TestTable (name text, age integer, primary key (ROWID))

The error message is:

11-23 11:05:05.298: ERROR/Database(31335): Failure 1 (table TestTable has no column named ROWID) on 0x2ab378 when preparing 'create table TestTable (name text, age integer, primary key (ROWID))'.

However, after the TestTable is created, this prepares and executes just fine:

create table TestTable (name text, age integer);

insert into TestTable (name, age) values ('Styler', 27);

select * from TestTable where ROWID=1;

I could potentially see ROWID as being a solution to needing an auto-increment primary key and foreign key which are never going to be used as populated as data on the application layer. Since ROWID is hidden from select result sets by default, it would have been nice to associate this with the primary key while keeping it hidden from the application logic. OracleBlog: ROWNUM and ROWID say this is impossible and inadvisable, but doesn't provide much explanation other than that.

So, since the answer to 'is this possible' is definitely no/inadvisable, the question is more or less 'why not'?

Faheel
  • 2,435
  • 24
  • 33
Tyler
  • 19,113
  • 19
  • 94
  • 151
  • 1
    ROWNUM changes whenever the record is updated, ROWID changes based on sort or the order of your result set. So RowNum is bad because you would hav eto update all foreign keys each time a change is made to the master. (lots of overhead) and ROWID is bad because the ID doens't exist until the result set is created and ordered. If it doesn't exist, you can't join to it. – xQbert Nov 23 '11 at 17:49
  • @xQbert if ROWID is being referred to by a primary key, how would it change based on the order of columns in the result set? – Tyler Nov 23 '11 at 17:56
  • ROWID doesn't exist in your table describe testtable. you'll see ROWID isn't there. ROWID is a system column and system maintained value. you can't have a PK on a system column; you don't have the permissions to do it. About the above: i have it backwards; ROWID can change based on update vs ROWNUM which changes based on whereclause select. – xQbert Nov 23 '11 at 19:53

1 Answers1

48

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.

varun
  • 2,027
  • 1
  • 15
  • 17
Eugene
  • 3,280
  • 21
  • 17
  • 2
    By adding any auto incrementing primary key, I am essentially using ROWID as the primary key. Was thinking since it was that easy I could just declare the rowid as primary. Obviously theres a little more going on than that. That's what I get for thinking different, shame on me :D – Tyler Nov 23 '11 at 17:52
  • 6
    [Adding an auto-increment key changes the behavior slightly](http://www.sqlite.org/autoinc.html). The ROWID is not always suitable, primarily if monotonically increasing values are a requirement. – user2864740 Jun 07 '14 at 09:14