Unable to INSERT in SQLite, Error code:19

Question

When I'm trying to run the following :

  ContentValues cv = new ContentValues();
  cv.put(table_LocalSettings_Unit, input);
  mDb.insert(table_LocalSettings, "", cv);

I got the following error:

Error inserting unit = 0;
SqliteConstraintException: error code 19 constraint failed.

What should be the problem ? The table sql Code is:

 "create table if not exists " + table_LocalSettings + "( " + table_LocalSettings_ID
     + " INTEGER PRIMARY KEY NOT NULL , " + table_LocalSettings_MapType
     + " INTEGER NOT NULL , " + table_LocalSettings_Visib + " BIT NOT NULL , "
     + table_LocalSettings_Unit + " INTEGER DEFAULT 0 NOT NULL , "
     + table_LocalSettings_SpeedUnit + " INTEGER NOT NULL , "
     + table_LocalSettings_Alert + " BIT NOT NULL ," + table_LocalSettings_UserID
     + " INTEGER DEFAULT -1 , " + table_LocalSettings_Username + " VARCHAR , "
     + table_LocalSettings_PowerSave + " VARCHAR , " + table_LocalSettings_PremiumUser
     + " INTEGER NOT NULL DEFAULT 0);";

Solution 1

constraint failed

Sounds like your primary key already exists in the table

Solution 2

I had the same problem and Pentium 10's answer led me in the correct direction. After verifying the bellow code was wrong then correcting it I cleared data from that app in the emulator and it recreated the DB and it works fine now.

     "create table if not exists " + DATABASE_TABLE + " (" + _ID + " integer primary key autoincrement," +
     " ItemName text not null, ItemID text not null, Image text not null, ImageDate text not null);";

I think one of my problems was I had an extra column. I removed one of the columns earlier and did not remove it from the above lines.

The main thing is tripple check the code for errors and spelling and if using the emulator clear the data.

Solution 3

You can remove the not null from table and it will work fine. like this:
right:

String callTable = "CREATE TABLE IF NOT EXISTS '%s'" + "(userid VARCHAR, callwith VARCHAR, calltype VARCHAR, callstart time, callend time, callmedia VARCHAR" + ");"

wrong:

String callTable = "CREATE TABLE IF NOT EXISTS '%s'" + "(userid VARCHAR not Null , callwith VARCHAR not null, calltype VARCHAR, callstart time, callend time, callmedia VARCHAR" + ");"

Solution 4

You can see a list of all the SQLite error codes here http://www.sqlite.org/c3ref/c_abort.html. Error code 19 means a table constraint (NOT NULL, UNIQUE, etc.) was violated during the operation (INSERT, etc.). From looking at your table creation logic, many of your fields are set to NOT NULL, yet you are only attempting to insert a single column. If you create your table such that values cannot be null, you have to include a non-null value during the INSERT, otherwise you will see the error code 19. You could also remove the constraint from your database table if it isn't needed.

As a side note, there are other insert methods that allow for handling the constraint violation such as

db.insertWithOnConflict(..., ..., ..., INTEGER);

where INTEGER is one of the static conflict resolution variables in the SQLiteDatabase class (but I don't think any of them allow for NOT NULL violation). You can also read more about SQLite conflict resolution options here: http://www.sqlite.org/conflict.html

View more solutions

Comments

[narancs]

I had updated the first question. Do you see something wrong on my sql ? I can't !

[Pentium10]

Sounds like you are not attaching to the contentvalues value for the primary key. And this is fails as the table creation is missing the autoincrement directive, it would be something like: '_id' integer PRIMARY KEY AUTOINCREMENT NOT NULL

[Pentium10]

But that goes for another column, for table_LocalSettings_Unit. I don't see you insert value for column table_LocalSettings_ID. Add that autoincrement directive to your table creation and probably will start working. Megertetted hogy miert tortenik igy?

[narancs]

unfortunately don't works. The offical sqlite documentation said:"INTEGER PRIMARY KEY" also include AUTOINCREMENT keyword. So, I don't need to add it:(

[Pentium10]

Take the table off the device, and test in a SQLite engine and see if works. I still believe your autoincrement stuff is broken.

[Hobbyist]

There is nothing wrong with NOT NULL being used. It states that a value is not allowed to be null.