Search code examples
androidsqliteandroid-sqlite

How is it possible to insert row into SQLite table without specifying value for the primary key


I'm working on a project and don't understand this part of this code that I found online. (I have also looked at other examples and they do the exact same thing but I don't quite understand why)

When they are inserting something into the table, they have no value for the primary key. Could someone explain to me why that is the case?

Here is 2 examples of code that I found that do what I have stated above. Thanks.

// As you can see a contact has 3 attributes.
int _id;
String _name;
String _phone_number;


// Where they create a table. As you can see the primary key is ID
@Override
public void onCreate(SQLiteDatabase db)
{
   String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
    + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_PH_NO + " TEXT" + ")";
       
    db.execSQL(CREATE_CONTACTS_TABLE);
}
       

// Adding new contact
// This is what I don't understand. Why don't they get an ID for the contact. 
// They only have values for the name and phone number when they insert it into the table.
public void addContact(Contact contact) 
{
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Contact Name
    values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

    // Inserting Row
    db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Closing database connection
}

Here's another example but this is using a book. A book has 3 attributes, an id (the primary key), an author and the book name. And once again, they don't get the value for the primary key.

public void addBook(Book book)
{
    Log.d("addBook", book.toString());
    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put(KEY_TITLE, book.getTitle()); // get title 
    values.put(KEY_AUTHOR, book.getAuthor()); // get author

    // 3. insert
    db.insert(TABLE_BOOKS, // table
        null, //nullColumnHack
        values); // key/value -> keys = column names/ values = column values

    // 4. close
    db.close(); 
}

Solution

  • because primary key is Autoincrement as it is an alias for ROWID.

    from the documentation:

    In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table. (WITHOUT ROWID tables are the exception.)

    You can access the ROWID of an SQLite table using one the special column names ROWID, ROWID, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.

    If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

    When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:

    so in the examples you have given id is being assigned by database engine. for most of the use cases this is good enough.