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.

This is common because you have kept percentage as primary key, and when you are inserting data primary key is getting duplicated, better take Base._ID as primary key which will auto increment, and if you want to use your own then make sure they are not getting duplicated. — Silvans Solanki, Aug 27 '16 at 14:13
It is quicker and better to Google your exceptions and errors than posting it on StackOverflow before doing anything. **A.** you will avoid downvotes, **B.** this is how you will improve your search queries. — Sufian, Aug 27 '16 at 14:51
Possible duplicate of [sqlite constraint exception primary key must be unique](http://stackoverflow.com/questions/26986687/sqlite-constraint-exception-primary-key-must-be-unique) — Sufian, Aug 27 '16 at 15:20
@Sufian: Thanks for your response. I am still learning and i will make sure to use exceptions in code to better understand my errors. But my counter will update every time. And I guess it will be unique every time it will provide data to insert in database. — Raj krishna, Aug 27 '16 at 16:11
@Rajkrishna if you have data in the Table beforehand, it will not work because the initial value of `serial` will have been assigned to some row. The answer you selected will work nicely because it is using `AUTOINCREMENT`. — Sufian, Aug 27 '16 at 16:17

score 1 · Answer 1 · answered Aug 27 '16 at 14:12

1

Serial column accept only unique value.

Just update

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

answered Aug 27 '16 at 14:12

Yogesh Rathi

6,331
4
51
81

score 0 · Answer 2 · answered Aug 27 '16 at 14:14

0

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

answered Aug 27 '16 at 14:14

meda

45,103
14
92
122

if u dont mind can u pls see this [question..](http://stackoverflow.com/questions/39268248/access-email-id-along-with-name-and-phone-number-from-contact-list-in-android).. – sunil y Sep 02 '16 at 09:02

score 0 · Accepted Answer · answered Aug 27 '16 at 14:16

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

score 0 · Answer 4 · answered Aug 27 '16 at 14:17

0

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

answered Aug 27 '16 at 14:17

Bhagwat K

2,982
2
23
33

Thanks, its working now. I have removed serial as id is present to provide serial number. – Raj krishna Aug 27 '16 at 16:14

SQLiteConstraintException when inseting to database

4 Answers4