database - Do i have to use _ID as a SQlite primary key? and does it have to be an INT? (Android Dev)

Question

This is probably a silly question but i haven't been able to find the answer yet.

I want to use a TEXT column with my own unique names as the primary key in a table. the little section of code to define this looks something like this in my project:

...blahblah..."CREATE TABLE " + CAT_BUD_TAB + " (" + CAT_ITEM_ID + "_ID TEXT PRIMARY KEY,    "...blahblah..

Will this work as i intend? Or perhaps i need to use "AS ID"? I only ever see single tables with _ID as an autoincrementing integer in. Also this was intended to be a foreign key in another table but since i designed my database i've read more information and i'm not sure that'll really matter using android & SQLITE?

Thanks to the posters below but i'm a bit slow and not sure if i'm applying the info right, could you check?

So if i have a create statement like this:

"CREATE TABLE " + CAT_BUD_TAB + " (" + CAT_ITEM_ID + " TEXT PRIMARY KEY, " + 
    IN_OUT + " TEXT, " + BUDGET_AMOUNT + " REAL, " + ACTUAL_AMOUNT_SPENT + " REAL, "
    + AMOUNT_STRAYED + " REAL, " + OVERBUDGET_TF + " INTEGER, " + AUTOSPEND_TF + 
    " INTEGER);"

Then have: db.execSQL("SELECT ID, ID AS CAT_ITEM_ID")

Can i then use them interchangeably? am i even anywhere near close? haha sorry i am trying!

Answer 1

You can create and define tables as you wish, but if you will have to use the table via a CursorAdapter that won't work without a tweak.

CursorAdapter: The Cursor must include a column named "_id" or this class will not work.

You must always issue a select col1 as _id ... to work.

So it's recommended to use _id in the table schema, and if you like to use by some other name you could this this. select _id, _id as customname ... so select the _id column twice with different names.