8

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!

Holly
  • 1,956
  • 6
  • 41
  • 57

3 Answers3

12

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.

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Thank you for the response! But i'm still a bit slow i'm afraid, if you could check my use of your advice in my original post i'd really appreciate it! – Holly Nov 30 '10 at 13:43
  • Your code looks fine but the field must be `_ID` and not `ID` – Pentium10 Nov 30 '10 at 13:46
3

You can have anything you want as the primary key.

But if you for instance want to use a CursorAdapter that automatically shows items from a Cursor-query you need to have an integer column called _ID as this is used here.

But if this is not something you plan to use with your table, you can do what you want!

Gober
  • 3,632
  • 3
  • 26
  • 33
  • Thanks! But i think i will be needing a CursorAdapter, so i have to have an integer huh. okay cheers. Can i have the INT _ID but not really use it, that is, it's possible for me to say "retrieve the whole record where this TEXT column is == "Food" right? Doesn't really matter anyway thank you for the help! – Holly Nov 30 '10 at 13:47
0

_id must be of type int in sqlite create (long in Java). Various classes assume that. As a result, you cannot use UUID for primary key.

eugene
  • 320
  • 3
  • 10