SQLite Abort due to constraint violation April 17, 2010Posted by Ameya in iPhone App Devlopment.
Tags: 19, abort, Abort due to constraint violation, SQLite, violation
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.
- 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.
- 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.
- 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.