The default behaviour of INSERT when running into a PRIMARY KEY, UNIQUE or NOT NULL constraint is ABORT, i.e. back out any changes made by the current statement and return an error.
You may select a different behavior if your application logic so requires, the other choices being ROLLBACK the complete transaction FAIL stop processing but keep any changes before the offending row IGNORE keep the old data REPLACE keep the new data // make sure a record in the other_table exists but keep any data already present INSERT OR IGNORE INTO other_table (<key fields>) VALUES (<key values>); // support data entry without keeping a write transaction open SELECT * FROM this_table; <display data on entry form and wait for user to finish> INSERT OR REPLACE INTO this_table (<all fields>9 VALUES (<new values>); -----Ursprüngliche Nachricht----- Von: techi eth [mailto:[email protected]] Gesendet: Freitag, 30. August 2013 11:43 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Table Creation Behaviour!!! Thanks. What about INSERT query behavior. Is their any specific reason of allowing inserting same data set which is already present in database ? Cheers - On 8/30/13, Clemens Ladisch <[email protected]> wrote: > techi eth wrote: >> I have open the connection, created the table by using CREATE TABLE >> IF NOT EXISTS , doing some operation & closing the connection. >> >> If I again open the connection & try to do table creation again (With >> same table name & same database file) then what will be the behavior? >> >> I am expecting it should not create table & return me error, because >> their is already table with same name exists. > > RTFM <http://www.sqlite.org/lang_createtable.html>: > | It is usually an error to attempt to create a new table in a > | database that already contains a table of the same name. However, if > | the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE > | statement and a table of the same name already exists, the CREATE > | TABLE command simply has no effect (and no error message is returned). > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: [email protected] This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
