How to avoid "SQL runtime error: constraint failed" when updating sqlite kernel data store?

Question

Our application allows the user to select a location from the list provided by the API. The list is rarely updated, and only by adding elements, so instead of hitting the API each time, the application comes with a snapshot in the sqlite Core Data repository, and we want it to update the list periodically. The code for this works as follows:

• create a managed object context for a stream
• get the full list from the API
• for each of them:
  • find the location in the context with the appropriate locationID
  • if not found, insert a new one in context
  • update location with new information
• keep context

When starting from an empty database, this works fine. However, when we run it a second time, it fails during save with the message “SQL runtime error: constraint failure”. He does this even if I limit him to one place. If I turn on SQL debugging, I see the following:

CoreData: sql: BEGIN EXCLUSIVE CoreData: sql: COMMIT CoreData: sql: BEGIN EXCLUSIVE CoreData: sql: INSERT INTO ZLOCATION(Z_PK, Z_ENT, Z_OPT, ZGEOID, ZCOUNTY, ZCOUNTRYCODE, ZNAME, ZLATITUDE, ZLONGITUDE, ZLANGUAGECODE) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) CoreData: error: (19) constraint failed CoreData: annotation: Disconnecting from sqlite database due to an error. 

Then he recovers again and again and tries to refuse again.

My code definitely finds the old locations and all the objects are valid - or at least [object validateForUpdate] returns YES. What does the error mean? Is there a way to find out which constraint fails?

If I use a binary, the error disappears, but the binary is atomic and blocks writing for centuries. This seems like an error in the sqlite store - has anyone found a workaround?

Answer 1

I noticed that he was doing INSERT INTO ZLOCATION not UPDATE ZLOCATION , so I looked at the bit where I inserted the locations in the context. I have had:

 if ([object isInserted]) { if (![object validateForUpdate:&error]) { NSLog(@"Invalid object %@: %@, %@", object, error, [error userInfo]); break; } } else { if ([object validateForInsert:&error]) { [context insertObject:object]; } else { NSLog(@"Invalid object %@: %@, %@", object, error, [error userInfo]); break; } } 

I did not know that for objects exiting the database, [object isInserted] is false . So, I inserted the already inserted object, and as a result it crashed. When I changed it to [object managedObjectContext], my problem disappeared.

Answer 2

This is most likely a validation error. If you have a custom verification code, check this out.

An amazing number of strange context errors are caused by threading issues. I would run the code on the front thread. If this works, you have a thread issue.