Raj krishna Raj krishna - 3 months ago 11
Android Question

SQLiteConstraintException when inseting to database

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

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