Understanding the "_id" Field in Android SQLite

In a nutshell:

• The _id field is a convention in Android development to refer to this rowid. It's not mandatory, but it helps with compatibility with certain Android UI components like CursorAdapter.
• SQLite, a lightweight database engine included in Android, assigns a unique identifier (rowid) to each row in a table by default (even if you don't define a primary key).

Here's a breakdown:

• Optional but helpful: While not strictly required, using _id can be beneficial:

  • Compatibility: Certain Android UI components, like CursorAdapter used for managing lists, expect a cursor with a column named _id. Using this convention ensures your database works seamlessly with these components.
  • Code clarity: It makes your code more understandable by clearly indicating the purpose of the field (referencing the unique row identifier).

Alternatives:

• If you define a primary key for your table, that primary key becomes the actual unique identifier, replacing the rowid's role. However, some tools and libraries might still use _id internally.

Key points:

• It enhances compatibility with Android UI components.
• It provides a way to reference the rowid for easier data manipulation.
• _id is a convention, not a mandatory field.

Creating a Table with "_id":

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String TABLE_NAME = "CONTACTS";
    public static final String _ID = "_id";
    public static final String NAME = "name";
    public static final String PHONE = "phone";

    public DatabaseHelper(Context context, String dbName, int version) {
        super(context, dbName, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
                _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                NAME + " TEXT, " +
                PHONE + " TEXT " +
                ")");
    }

    // Other methods for upgrading database (if needed)
}

In this example:

• _id is declared as an INTEGER PRIMARY KEY AUTOINCREMENT, which creates the unique rowid and automatically increments its value for each new row.
• We define a table named "CONTACTS" with three columns: _id, NAME, and PHONE.

Inserting Data with "_id":

public long insertContact(DatabaseHelper db, String name, String phone) {
    SQLiteDatabase writableDatabase = db.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(DatabaseHelper.NAME, name);
    values.put(DatabaseHelper.PHONE, phone);
    long rowId = writableDatabase.insert(DatabaseHelper.TABLE_NAME, null, values);
    writableDatabase.close();
    return rowId;
}

Here, we:

• insert() method returns the row ID (which is the _id value) of the newly inserted row.
• Insert a new contact into the "CONTACTS" table.

Retrieving Data using "_id":

public Cursor getContactById(DatabaseHelper db, long id) {
    SQLiteDatabase readableDatabase = db.getReadableDatabase();
    String[] projection = {DatabaseHelper._ID, DatabaseHelper.NAME, DatabaseHelper.PHONE};
    String selection = DatabaseHelper._ID + " = ?";
    String[] selectionArgs = {String.valueOf(id)};
    return readableDatabase.query(DatabaseHelper.TABLE_NAME, projection, selection, selectionArgs, null, null, null);
}

This code:

• We use _ID in the WHERE clause of the SELECT query to filter based on the provided ID.
• Retrieves a specific contact by its _id.

Using a Custom Primary Key:

• Example (modifying the table creation code):
• Instead of relying on the implicit rowid, you can define your own column as the primary key. This column would hold a unique identifier for each row, typically a string or an integer you generate.

public static final String USER_ID = "user_id"; // Your custom identifier

...

db.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
            USER_ID + " TEXT PRIMARY KEY, " +
            NAME + " TEXT, " +
            PHONE + " TEXT " +
            ")");

• Drawback: Requires additional logic to generate and manage the custom IDs.
• Benefit: Provides more control over the unique identifier format.

Composite Primary Key:

• Example:
• Combine multiple columns to form a unique identifier. This can be useful when a single column might not be sufficient for guaranteeing uniqueness.

public static final String USER_ID = "user_id";
public static final String EMAIL = "email";

...

db.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
            USER_ID + " TEXT, " +
            EMAIL + " TEXT, " +
            PHONE + " TEXT, " +
            "PRIMARY KEY (" + USER_ID + ", " + EMAIL + ")" +
            ")");

• Drawback: Might be less efficient for querying compared to a single primary key.
• Benefit: Ensures uniqueness even for rows with identical values in some columns.

No Primary Key:

• In specific scenarios, you might not need a strict primary key. However, this is generally discouraged as it makes it harder to uniquely identify and manage rows.

Choosing the Right Method:

• Avoid omitting a primary key unless absolutely necessary for your use case.
• For more control or complex identification needs, consider custom primary keys or composite keys.
• If a simple unique identifier suffices, _id (rowid) is a convenient option.
• The best approach depends on your specific data model and needs.