2

Looking at this answer I was trying to work out how to actually create a table with a column that is just an alias for ROWID. I would like to do this as some android classes require a unique column named '_id' but i dont want that col to be a PRIMARY KEY. I cant use AUTO_INCREMENT on the col that not a PK either, so I would just like _id to be an alias. I know I could do that in the query but thats not practical for my situation. Any pointers? Is this even possible?

Thanks :)

EDIT: really i just want to be able to have my own PK but also have an id field for CursorAdapters to work correctly etc

EDIT: Looking at Do i have to use _ID as a SQlite primary key? and does it have to be an INT? (Android Dev) it suggests to do it in the query if my PK is a number type also, but what if its a TEXT col that im using for my PK? (sort of thinking aloud here) - I guess i could copy CursorAdaptor and just getString instead of a long for the _id col (plus, pass the name of the col to use as the PK in to cursor adapter, get rid of _id!) OR just add a alias for the ROWID as _id in the SELECT, feels a bit hacky though...

Community
  • 1
  • 1
Dori
  • 18,283
  • 17
  • 74
  • 116

2 Answers2

2

You may have a primary key that is completely independent of SQLite's built-in rowid.

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name.

You may formulate your query so that the built-in rowid is called _id for the benefit of the CursorAdaptor by prepending the hidden rowid column to your results:

select _rowid_ as _id, ...

The rowid is unique, but need not be the primary key. You can make the primary key anything you like in the CREATE TABLE statement.

Doug Currie
  • 40,708
  • 1
  • 95
  • 119
  • +1 thanks for the answer. I was aware of this as a possible solution but wanted to see if there was any other solution. Wish they has just done it so you could pass in a PK col name to the adapter and it would pass the value out instead of the sometimes-awkward _id value – Dori Jul 09 '12 at 11:03
0

TBH, I don't think that this is really feasible. You are required to have that kind of PK column (even though with a lot of hacking you could create some kind of workaround).

I would rather suggest to have the _id column as a technical PK and have an additional column as logical PK i.e. just a freely defined column with a unique constraint. Your program logic should then be able to simply use the logical PK for all operations. Of course this would require you to use custom queries for find operations etc. but that is usually more fitting anyways.

hth..

Candlejack
  • 248
  • 1
  • 3
  • 15