SQLiteConstraintException when inseting to database

Question

I have tried to insert data into database in android.

This one works:

public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table study " +
                    "(percentage integer primary key,videoDuration text,totalTime text,date text)"
    );
}


public boolean insertStudy(String percentage, String videoDuration, String totalTime, String date) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("percentage", percentage);
    contentValues.put("videoDuration", videoDuration);
    contentValues.put("totalTime", totalTime);
    contentValues.put("date", date);
    db.insert("study", null, contentValues);
    return true;
}

But when I try to add one more column for serial number (as i dont have any column for primary key) I get error while inserting.

This is new code (not working):

public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table study " +
                    "(serial integer primary key,percentage text,videoDuration text,totalTime text,date text)"
    );
}


public boolean insertStudy(String percentage, String videoDuration, String totalTime, String date) {
    serialNumber++;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("serial", serialNumber);
    contentValues.put("percentage", percentage);
    contentValues.put("videoDuration", videoDuration);
    contentValues.put("totalTime", totalTime);
    contentValues.put("date", date);
    db.insert("study", null, contentValues);
    return true;

}

I am using serialNumber as counter so that it will keep inserting values in serial and also act as primary key. I am getting errorfor this code:

Error inserting date=27/08/2016 percentage=2.6 serial=1 totalTime=0:0:1 videoDuration=22:96
android.database.sqlite.SQLiteConstraintException: PRIMARY KEY must be unique (code 19)

I don't understand why first is working but not second. I want to understand why second code is not working.

My application can work with first code but it might result in bugs in future so I want to use second code.

Answer 1

Try it, adding autoincrement and removing the setting of the id manually:

db.execSQL(
        "create table study " +
                "(serial integer primary key AUTOINCREMENT ,percentage text,videoDuration text,totalTime text,date text)"
);

public boolean insertStudy(String percentage, String videoDuration, String totalTime, String date) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("percentage", percentage);
    contentValues.put("videoDuration", videoDuration);
    contentValues.put("totalTime", totalTime);
    contentValues.put("date", date);
    db.insert("study", null, contentValues);
    return true;
}

public boolean insertStudy(String percentage, String videoDuration, String totalTime, String date) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("percentage", percentage);
    contentValues.put("videoDuration", videoDuration);
    contentValues.put("totalTime", totalTime);
    contentValues.put("date", date);
    db.insert("study", null, contentValues);
    return true;
}

Answer 2

Serial column accept only unique value.

Just update

db.execSQL("create table study (serial integer,percentage text,videoDuration text,totalTime text,date text)");

Answer 3

Percentage cannot be used a primary key, like the error suggested it should be unique, recommended approach is to add id field.

db.execSQL(
"create table study " +
"(id integer primary key, percentage,videoDuration text,totalTime text,date text)"
);

Answer 4

While creating the table take id and it as make Primary key Auto-increment :

   db.execSQL("create table study (id integer primary key autoincrement, serial integer,percentage text,videoDuration text,totalTime text,date text)"
);