Is the field "_id" necessary in Android SQLite?
-
1for any one else hitting the page check this https://www.sqlite.org/autoinc.html this is really useful. Specifically read the ` ROWID` thing. – Muhammad Babar Nov 20 '14 at 07:56
-
The correct answer would be `NO`. Because SQlite database Engine already has a mechanism that creates a unique `ROWID` for every new row you insert. And if you table have a `PRIMARY_KEY` then it will eventually becomes the alias for that `ROW_ID`. – Muhammad Babar Nov 20 '14 at 08:00
9 Answers
_id is useful when you are using the enhanced Adapters which make use of a Cursor (e.g. ResourceCursorAdapter). It's used by these adapters to provide an ID which can be used to refer to the specific row in the table which relates the the item in whatever the adapter is being used for (e.g. a row in a ListView).
It's not necessary if you're not going to be using classes which need an _id column in a cursor, and you can also use "as _id" to make another column appear as though it's called _id in your cursor.

- 3,904
- 1
- 22
- 17
Why not make use of _ROWID_?
SQLite provides this anyway for every row, so you can just alias it to _id in your select statement.

- 8,377
- 2
- 25
- 34
-
1Nice trick. If you create a table with _id (being mapped to ROWID), it's more straight forward though. – Markus Junginger May 22 '12 at 10:45
-
2
-
@greenrobot - thanks for that. I was looking for a way to have _id but *NOT* as a `primary key` and still be `unique` and `auto increment` - but i dont think this is possible without an external counter which is not ideal :( – Dori Jul 09 '12 at 09:37
Technically no the field _id
is not required, however if you are making use of the CursorAdapter
class (which you probably are, especially if you are working with the Notepad example) then yes
"The Cursor must include a column named "_id" or this class will not work"
as explained in the documentation here. Unfortunately the code examples do not make this very clear.

- 4,384
- 3
- 34
- 44
It's quite convenient in many cases to have an id field. I prefer mine to be auto-incrementing (as shown below). I'm always finding new uses for the id field :)
When it comes time to attach the data to an adapter, I like to use a table name alias to query the id field as _id
. Example: SELECT id _id, msg from message order by id
. That way the adapter sees a field called _id
and everybody's happy.
Here's a sample of how I define my tables:
CREATE TABLE message (_id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp INTEGER, tripID TEXT, msg TEXT);
-
1
-
3note that `AUTOINCREMENT` is unnecessary unless you want to ensure that during the lifetime of the database the same id won't be reused. http://www.sqlite.org/autoinc.html – forcewill Sep 26 '14 at 14:18
-
-
From the official docs...
The Cursor must include a column named "_id" or this class will not work. Additionally, using MergeCursor with this class will not work if the merged Cursors have overlapping values in their "_id" columns.
And the Cursor
is:
This interface provides random read-write access to the result set returned by a database query.
In other words, you need _id
for Android SQLite ( which usually uses Cursor )

- 5,866
- 1
- 41
- 41
-
Do you know `_id` should be integer or a `_id VARCHAR PRIMARY KEY` is enough? – János Jun 21 '16 at 11:00
If you define your _id column as an autoincrementing integer it is actually an alias for the ROWID column that SQLite provides by default (https://www.sqlite.org/lang_createtable.html#rowid).
Your create statement needs take the form...
CREATE TABLE t(_id INTEGER PRIMARY KEY ASC, y, z);
To prove this works...
UPDATE t SET _id=22 WHERE _id=11;
then
SELECT ROWID, _id FROM t;
and you'll find both _id
and ROWID
have the same value.
Note, that if you use DESC in the CREATE a new column is created and ROWID
is not aliased.

- 91,361
- 17
- 137
- 196

- 59
- 1
- 3
Surely not. Its a convenience field that some widgets like ListView uses to populate data. See this good article: http://www.casarini.org/blog/2009/android-contentprovider-on-sqlite-tables-without-the-_id-column/

- 21
- 1
Of course if you are creating your own UI widget and your own adapter, you don't have to name your primary key as "_id". It can be any name you want. But you would be responsible for managing your collections of UI widgets and binding them to the right row in your database. "_id" is only useful for ListView as Brad has pointed out.

- 341
- 2
- 6
The _id field is indeed necessary in sqlite, it will help you to select a particular data from sqlite.
SELECT name from table_name where _id = ?
And if your are creating a recyclerview/ listview and you want a detailed activity for that list item you indeed need an id for this to fetch data of that item.
if you are creating a class for constants there is a BaseColumn interface in android, which provide _ID field to that constant class.
//from android documentation..
public static class FeedEntry implements BaseColumns {
public static final String TABLE_NAME = "entry";
public static final String COLUMN_NAME_TITLE = "title";
public static final String COLUMN_NAME_SUBTITLE = "subtitle";
}

- 295
- 3
- 13