Why is this code raising the "CoreData: error: (19) PRIMARY KEY must be unique" error?

Question

This code raises the "CoreData: error: (19) PRIMARY KEY must be unique" error. The Day entity has only a when attribute which is an NSDate, and a to-many relationship called tasks. Why this error? If a Day with a specific date is already stored, I fetch it, otherwise I insert it. So, for each day object, there should be a different when attribute. I am not sure if this is the primary key though. How to solve this ? Thank you in advance.

   NSMutableSet *occurrences = nil;

   occurrences = ... 
   NSMutableOrderedSet *newSet = [NSMutableOrderedSet orderedSetWithCapacity:[occurrences count]];

   for(NSDate *current in occurrences) {

        NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];

        // try to find a corresponding Day entity whose when attribute is equal to the current occurrence
        // if none is available, create it

        Day * day = [[self getDayForDate:current inManagedObjectContext:moc] retain];
        if(!day){
            day = (Day *) [NSEntityDescription insertNewObjectForEntityForName:@"Day" inManagedObjectContext:moc];
        }

        day.when = current;
        [day addTasksObject:aTask];

        [newSet addObject:day];

        [moc insertObject:day];
        [moc processPendingChanges];

        [day release];

        [pool release];            
    } 

- (Day *)getDayForDate:(NSDate *)aDate inManagedObjectContext:(NSManagedObjectContext *)moc
{        
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"Day" inManagedObjectContext:moc];
    [request setEntity:entity];
    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"(when == %@)", aDate];
    [request setPredicate:predicate];
    NSError *error = nil;
    NSArray *array = [moc executeFetchRequest:request error:&error];
    [request release];

    Day *theDay = nil;

    if(array && [array count] == 1){
        theDay = [array objectAtIndex:0];
    }

    return theDay;
}

Answer 1

I have recently been struggling with the CoreData: error: (19) PRIMARY KEY must be unique error with an in-house iOS application, and thankfully discovered the solution after a day or two of research and code modifications, and I wanted to share my findings here in the hopes that they would be of help to others.

First, a little background

Our in-house app was never originally built with any code to update its CoreData store - instead for each app build iteration when new data was required to be displayed within the app, the CoreData SQLite backing store file was simply replaced with a new version that had been edited with a standard desktop-based SQLite editor -- that is the raw SQLite file was modified and updated as needed. While it is recognized that this approach does not take account of the "black-box" nature of CoreData, it had actually served us well with our simple app for the past few years, and the app had happily accepted the updated SQLite file with each new app build.

However, recently we undertook a major overhaul of the app, moving away from a model of updating app assets and data via Xcode and app rebuilds, to a model of obtaining new app data via a web service instead, and it was during this new development work that the PRIMARY KEY must be unique issue arose.

After several days of struggling to solve the issue, thinking that there must be some fault with the new CoreData entity-creation code (which had been checked and rechecked thoroughly) or some other related issue, I found through further research that I was able to enable CoreData SQL debugging for our app using Xcode (as per the instructions in this very helpful SO post).

When I carefully studied the SQL logs in Xcode, I could see that before each call that CoreData made to INSERT a new record into the SQLite backing store, that the framework queried the Z_PRIMARYKEY table with the following query SELECT Z_MAX FROM Z_PRIMARYKEY WHERE Z_ENT = ? where ? is replaced behind the scenes with the relevant Z_ENT value for the relevant CoreData entity (you can see the Z_ENT value for each of your CoreData entities by reviewing the contents of the Z_PRIMARYKEY table). This is when I finally understood what was happening within CoreData's black box! I then took a closer look at our app's SQLite file using the Liya app on the Mac, and I reviewed the contents of the Z_PRIMARYKEY table, and sure enough the Z_MAX column values were all set to 0. They had never been changed from their initial values when CoreData had first generated the empty SQLite backing store file for our app!

I immediately realised what was going wrong and just why CoreData had reported the primary key error as it had -- it was not in fact related to any higher-level CoreData entity object's attributes clashing somehow as initially suspected, but was indeed a lower-level error. Only now did the error make absolute sense, it had been there all along, it had just not been understood within the correct context.

As it turned out from further research, our in-house team had been successfully making direct edits to the SQLite backing store for the past few years, inserting, updating and deleting records from the various entity tables, BUT our team had never made any changes to the Z_PRIMARYKEY table, and due to the way that our app had been utilising CoreData in a read-only fashion, this had never been an issue.

However, now that our app was trying to create and save CoreData entries back to the SQLite backing store, many of the INSERT queries which were being generated as a result simply failed because CoreData was unable to obtain the correct maximum primary key value from which to generate the next sequential primary key on any given table, and as such the INSERT query would fail with the now understandably meaningful PRIMARY KEY must be unique error!

Solving the Error

I realise that each developer may go about generating the default/initial CoreData content for their apps in various ways, however, if you have ever come across this particular error in CoreData and also struggled to find an immediately clear answer, I hope the following thoughts are of help:

• Our app's CoreData backing store was manually updated via directly editing the SQLite file (inserting, updating, and deleting records from the entity tables) - this approach had worked for a long time because of the read-only way we had been consuming this data within our app. However this approach failed to truly recognize the abstract "black box" nature of CoreData and that fact that SQLite records are not equivalent to CoreData entities, and that SQLite joins are not equivalent to CoreData relationships, and so on.

• If your app uses any kind of pre-populated CoreData store, and if you are populating the contents of your SQLite backing store in any way other than through CoreData -- make sure that if you do create new records in any of your CoreData entity tables, that you also ensure that you update the relevant record in the Z_PRIMARYKEY table, setting the Z_MAX column value to the current maximum Z_PK value in the relevant entity table.

  For example if you have a CoreData entity called Employee, within your CoreData SQLite persistent store backing file this will be represented by a table named ZEMPLOYEE - this table will have some 'hidden' CoreData columns including Z_PK, Z_ENT, Z_OPT, etc, in addition to columns that represent your entity's attributes and relationships. This entity table will also have a corresponding record in the Z_PRIMARYKEY table with a Z_NAME value of Employee - thus when you add a new record directly to the ZEMPLOYEE table - make sure that after you are done adding records, that you go through each entity table and copy the maximum Z_PK value to the Z_MAX column of the Z_PRIMARYKEY table. The value you enter into Z_MAX should be the largest Z_PK value in the corresponding table; do not set Z_MAX to be equal to the value of Z_PK + 1, as this will not be what CoreData is expecting!

You can query for the maximum Z_PK value of any entity table with the following SQL:

"SELECT Z_PK FROM ZEMPLOYEE ORDER BY Z_PK DESC LIMIT 1"

This will give you the largest Z_PK value for any record in the ZEMPLOYEE table - obviously you should replace ZEMPLOYEE with the relevant table name for your app's data model.

For our app, I was able to write a simple command line script that reads the SQLite file directly, iterating through each record of the Z_PRIMARYKEY table and updating it with the correct Z_MAX value. Once this was done, I was able to use this updated file as the app's persistent store backing file. Now, when I insert and save new CoreData entities, everything works as expected.

Now that our app has a way to directly request new data through a web-service we will be moving away entirely from manually editing the SQLite backing store and exclusively using the CoreData frameworks to update the data, but for times, or other apps, where such quick-and-easy data entry may be needed, I hope this answer can help out other developers and save them the time and the effort that it took me to discover this solution.

Answer 2

I guess you don't need to insert a new Day if you already have it (this is the case when day is not nil). In particular I'm referring to [moc insertObject:day].

If you use insertNewObjectForEntityForName, that method inserts the object for you when you save the moc. If you need to modify it (you have retrieved a non-nil day) modify it and save. In additon, I will do processPendingChanges when the loop finishes (just for performance reasons).

Hope that helps.