About "_id" field in Android SQLite

Is the field "_id" necessary in Android SQLite?

_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.

Why not make use of http://www.sqlite.org/autoinc.html">\_ROWID\_</a>;?

SQLite provides this anyway for every row, so you can just alias it to _id in your select statement.

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.

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);

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 )

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.

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/

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.

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";
    }