You put a constraint on core data and your app bricked?

Just recently we added a core data constraint and received a lot of complaints. People were saying their app is not opening whatever they do. It was hard for us to reproduce that because the column we put constraint had duplicate data. It was really bad to tell thousands of customers to reinstall the app, so we had to find a way.

Here is the error you get for a constraint fail:

Error Domain=NSCocoaErrorDomain Code=134110

UserInfo={reason=Cannot migrate store in-place: constraint violation during attempted migration}

NSUnderlyingException=Constraint unique violation: UNIQUE constraint failed: TABLE.COLUMN, reason=constraint violation during attempted migration

Tears are dropped, hours are wasted and solution found. It’s ugly but it solves the problem. Answer was to connect to core data sqlite files before its initialized and remove duplicate entries. After that core data will be more than happy to put its constraint on clean data and let you open your app.

The sql query we will run is given below. It’s for an IM messages table. First, we’ll understand how it works and see an example of it.

1. Inner join subquery
   We are doing a group by with message id, so sqlite will give us our aggregate function results for message ids. In our case we want to get biggest primary key because biggest one is the most recent one. HAVING lets us filter that data by row count. If row count of a message id x is more than one, it will pass the filter and dup temp table will contain that message id with most recent primary key.
2. Subquery for IN
   At inner subquery we got all duplicated message ids and their latest primary keys. We tell sqlite to give us all the older rows with duplicated message ids. That was my requirement, to remove older ones.
3. Delete
   This should be easy to guess. We got all the old primary keys for duplicated message ids and will delete them.

Now, to see this in an example I’ve prepared a small example to give you an idea. It checks if sqlite is not compatible with the latest model and runs this query only if it has to. One downside is, you have to run this every time. It can be stopped with a user defaults variable though. I didn’t include it in example.

Do I recommend this? No. But it saves the day and let’s me move on. The last question to answer is performance. I was able to run this query in 400ms for 700.000 rows with indexed message id. So watchdog will not be mad at us for locking app.