How do I avoid getting "error during SQL execution : constraint failed" when I update my sqlite core data store?

Question

Our app lets the user choose a location from a list which is provided by an API. The list updates infrequently and only by adding items, so instead of hitting the API every time, the app comes with a snapshot in a Core Data sqlite store, and we want it to periodically update the list. The code to do so works like this:

• create a managed object context for the thread
• get the full list from the API
• for each one:
  • find the Location in the context with a matching locationID
  • if not found, insert a new one into the context
  • update the Location with the new information
• save the context

When starting with a blank DB, this works fine. However, when we run it a second time it fails during the save with the message "error during SQL execution : constraint failed". It does this even if I limit it to one location. 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 it reconnects and tries again a few times before giving up.

My code is definitely finding the old Locations and the objects are all valid - or at least [object validateForUpdate] returns YES. What does the error mean? Is there a way of finding out which constraint is failing?

If I use the binary store, the error goes away - but the binary store is atomic and blocks for ages on writes. It looks like a bug in the sqlite store - has anyone found a workaround?

Answer 1

I noticed that it was doing INSERT INTO ZLOCATION not UPDATE ZLOCATION, so I had a look at the bit where I was inserting the locations into the context. I 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;
    }
}

What I didn't know is that, for objects coming out of the database, [object isInserted] is false. So, I was inserting an already-inserted object and it was crashing as a result. When I changed it to [object managedObjectContext], my problem went away.

Answer 2

It's most likely a validation error. If you have custom validation code, check that.

A suprising number of weird errors with context result from problems with threads. I would run the code on the front thread. If it works, then you have a threading problem.