Prevent insertion if the records already exist in

Question

I am programming for iPhone and i am using SQLITE DB for my app.I have a situation where i want to insert records into the table,only if the records doesn't exist previously.Otherwise the records should not get inserted.

How can i do this?Please any body suggest me a suitable query for this.

Thank you one and all,

Answer 1

Hello Sankar what you can do is perform a select query with the record you wish to insert and then check the response via SQLite's SQLITE_NOTFOUND flag you can check whether that record already exists or not. If it doesn't exist you can insert it otherwise you skip inserting.

I hope this is helpful.

Answer 2

Looking at SQLite's INSERT page http://www.sqlite.org/lang_insert.html.

You can do it using the following syntax

INSERT OR IGNORE INTO tablename ....

Example

INSERT OR IGNORE INTO tablename(id, value, data) VALUES(2, 4562, 'Sample Data');

Note : You need to have a KEY on the table columns which uniquely identify a row. It is only if a duplicate KEY is tried to be inserted that INSERT OR IGNORE will not insert a new row.

In the above example if you have a KEY on id, then another row with id = 2 will not be inserted.
If you have a KEY only on id and value then a combination of id = 2 and value = 4562 will cause a new row not be inserted.

In short there must be a key to uniquely identify a ROW only then will the Database know there is a duplicate which SHOULD NOT Be allowed.

Otherwise if you do not have a KEY you would need to go the SELECT and then check if a row is already there, route. But here also whichever condition you are using on columns you can add them as a KEY to the table and simply use the INSERT OR IGNORE

In SQLite it is not possible to ALTER the table and add a constraint like UNIQUE or PRIMAY KEY. For that you need to recreate the table. Look at this FAQ on sqlite.org
http://sqlite.org/faq.html#q11