Android: SQLite Constraint failed issue

Question

In my app, I am using SQLite database. While inserting data in one of my tables, I am facing weird issue of "SQLite exception: error 19". For the first time data gets inserted but, when I try to insert data again, it throws exception.

My create table query:

"CREATE TABLE Tickets ("
            + "ID integer PRIMARY KEY NOT NULL,"
            + " UID varchar(40) NOT NULL DEFAULT 'not available'," + " OrderID int NOT NULL,"
            + " CarrierTicketNum varchar(15) NOT NULL,"
            + " TicketTypeID int NOT NULL," + " TankTypeID int NULL,"
            + " ProductObsGravity numeric NULL DEFAULT 0,"
            + " ProductHighTemp numeric NULL DEFAULT 0,"
            + " ProductLowTemp numeric NULL DEFAULT 0,"
            + " ProductObsTemp numeric NULL DEFAULT 0,"
            + " ProductBSW numeric NULL DEFAULT 0");

Insert Query:

public void insertTank(int OrderID, int TicketTypeID,
        String CarrierTicketNum, String TankNum, int TankTypeID,
        Double ProductObsTemp, Double ProductObsGravity, Double ProductBSW,
        Double GrossBarrels, Double NetBarrels) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("OrderID", OrderID);
    values.put("CarrierTicketNum", CarrierTicketNum);
    values.put("TankNum", TankNum);
    values.put("TicketTypeID", TicketTypeID);
    values.put("TankTypeID", TankTypeID);
    values.put("ProductObsTemp", ProductObsTemp);
    values.put("ProductObsGravity", ProductObsGravity);
    values.put("ProductBSW", ProductBSW);
    values.put("GrossBarrels", GrossBarrels);
    values.put("NetBarrels", NetBarrels);
    //try {
        db.insertOrThrow("Tickets", null, values);
    /*} catch (SQLiteException sqle) {
        Log.d(TAG, "SQLITException: " + sqle.getMessage());
    }*/
    db.close(); // Closing database connection
}

Following is the log detail:

09-08 13:54:57.610: E/AndroidRuntime(5713): FATAL EXCEPTION: main
09-08 13:54:57.610: E/AndroidRuntime(5713): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1617)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at com.dzo.dispatchcrude.driverapp.datalayer.DatabaseHandler.insertTank(DatabaseHandler.java:736)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at com.dzo.dispatchcrude.driverapp.NetBarrelActivity$1.onClick(NetBarrelActivity.java:103)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.view.View.performClick(View.java:3511)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.view.View$PerformClick.run(View.java:14110)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.os.Handler.handleCallback(Handler.java:605)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.os.Handler.dispatchMessage(Handler.java:92)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.os.Looper.loop(Looper.java:137)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at android.app.ActivityThread.main(ActivityThread.java:4424)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at java.lang.reflect.Method.invokeNative(Native Method)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at java.lang.reflect.Method.invoke(Method.java:511)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
09-08 13:54:57.610: E/AndroidRuntime(5713):     at 
dalvik.system.NativeStart.main(Native Method)

Read almost number posts but didn't got much help.

No, I want to insert new data every time. Is there anything wrong in syntax? — Nitish
– Nitish, Commented Sep 8, 2013 at 8:57
Put a breakpoint on the insert statement. Inspect values. Does it contain any nulls? — Simon
– Simon, Commented Sep 8, 2013 at 9:06

Stefano Munarini · Accepted Answer · 2013-09-08 13:43:33Z

9

There are 2 ways to resolve your issue.

First:

Declare PRIMARY KEY as autoincrement

Second:

When trying to insert a new row, provide different/unique ID for the new row.

To test it, please DELETE DATABASE before running again your app.

SOLUTION: This line should be:

+ "ID integer PRIMARY KEY AUTOINCREMENT,"

instead of

+ "ID integer PRIMARY KEY NOT NULL,"

edited Sep 8, 2013 at 13:43

answered Sep 8, 2013 at 9:02

Stefano Munarini

2,7152 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Nitish Over a year ago

In sqlite, AutoIncrement for primary key is not required, right? Although, I tried with it but didn't worked.

Stefano Munarini Over a year ago

Do you need to put NULL? Maybe, that could be the problem.

Stefano Munarini Over a year ago

If you put NOT NULL you can't put not null values, but if you put nothing you can put even null values or not null values.

Stefano Munarini Over a year ago

But your error is not this. You error is probably that you're trying to insert two times value in the same row (so same ID). Try do delete your database and re-run app with autoincrement for ID. It should work

Nitish Over a year ago

I tried, as you said. I deleted the database, and rerun my app with autoincrement. And the app crashed on second insert.

|

Ritesh Gune · Accepted Answer · 2013-09-08 09:09:06Z

1

android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed

You are getting constraint failed because you violated the constraint put on the field, like passing a null value into a column that you declare as not null when you create your table.

I think "ID integer PRIMARY KEY NOT NULL," this is causing you the problem, Please make sure it is autoincrement and the other values are not null as you put the Not null constraint.

answered Sep 8, 2013 at 9:09

Ritesh Gune

16.7k6 gold badges47 silver badges73 bronze badges

4 Comments

Nitish Over a year ago

But, I am not passing anything in Primary key. Also I tried with auto increment.

Nitish Over a year ago

But one of the field UID(please refer create table query) will be updated from server while syncing, I cannot put anything to it. Can it be cause of problem?

Ritesh Gune Over a year ago

Check the data you are inserting, and make sure two records do not have the same key.

Ritesh Gune Over a year ago

I don't see any problem other than two records with same key here.

Manoj Behera · Accepted Answer · 2016-07-20 18:26:31Z

1

if you are using ID as int Value than

"ID integer PRIMARY KEY AUTOINCREMENT"

instead of

"ID integer PRIMARY KEY NOT NULL"

if you are using ID as String Value than

use below line to insert value db.insertWithOnConflict(TABLE_NAME, null,values,SQLiteDatabase.CONFLICT_IGNORE);

(Where values is ovject of ContentValues object)

instead of

db.insert(TABLE_NAME, null, values);

keep coding....smile...

answered Jul 20, 2016 at 18:26

Manoj Behera

2,85524 silver badges13 bronze badges

Collectives™ on Stack Overflow

Android: SQLite Constraint failed issue

3 Answers 3

9 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related