jump to navigation

SQLite Abort due to constraint violation April 17, 2010

Posted by Ameya in iPhone App Devlopment.
Tags: , , , ,

When working with SQLite connections with iPhone , encounter a strange problem.

  • Records where not getting inserted into database .
  • Existing records could not be updated.
  • The log and NSAssertion showed now error.

Tried to debug and found that the SQLite message after committing to db was 19 (SQLITE_CONSTRAINT) , this did not convey much information. Drilled down the SQLite static for the message and found “Abort due to constraint violation” for meaage 19. Googled but could not find much of help out there, and my project was stranded. While checking every thing from scratch found that thee where condition enforced on the column of table NOT NULL, like  id was PRIMAY KEY AUTO INCREMENT NOT NULL. This was the cause of the issue , as I wanted the SQLite to generate id for me but also had said you require an id field for an insertion to complete. The same problem was with some other column.

So I decided to keep some guideline for dealing with SQLite and iPhone connections.

  1. Never create primary key column of table with both the restrictions ON (TUE) , that is  AUTO INCREMENT NOT NULL. This rule can ignored, if you want to manage your own primary key creation.
  2. Never create column of table with restrictions NOT NULL, unless you are absolutely sure you will always have a value for the column. This rule can be ignored if you are managing NULL value entries in UI input fields from SQLite connection. No one uses this approach, of checking NULL from SQL, as every one wants to check input from the view in the controller itself.  So this rule must not be broken.
  3. When dealing with sqlite3_step() don’t depend on SQLITE_ERROR (1) ( SQL error or missing database ) to catch error with your SQL statements. Better look for  SQLITE_DONE (101) ( has finished executing successfully), rest everything is an error, even constrain violation.

My post may look silly, for an expert but even an expert makes a silly mistake. 🙂



1. laurentiu - April 17, 2010

Nice Blog dude 😀 , Bookmarked !!

2. Ejiroghene Gbenedio - July 16, 2011

Good one!

3. David Morez - May 28, 2012

Thanks for your posts,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: