I think INSERT OR IGNORE is designed to insert a record into a table if a record with its primary key doesn't already exist. It's not an INSERT AND IGNORE ON ANY ERROR.
So: INSERT OR IGNORE INTO t2 VALUES (1,1) INSERT OR IGNORE INTO t2 VALUES (1,1) The above would not cause an error where, INSERT INTO t2 VALUES (1,1) INSERT INTO t2 VALUES (1,1) Would. As for the best way to do what you want, I'm not sure. Obviously, you could precheck the foreign key yourself, but I suspect your actual code is more sophisticated. Marc -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Daniel Polski Sent: Tuesday, November 25, 2014 10:23 AM To: General Discussion of SQLite Database Subject: [sqlite] insert or ignore with foreign keys I guess the example below shows the intended behaviour for Sqlite? PRAGMA FOREIGN_KEYS=1; CREATE TABLE t1 ( id INTEGER PRIMARY KEY ); CREATE TABLE t2( id INTEGER PRIMARY KEY, t1_id INT NOT NULL, CONSTRAINT fk FOREIGN KEY(t1_id) REFERENCES t1(id) ); INSERT INTO t1 VALUES(2); BEGIN TRANSACTION; INSERT OR IGNORE INTO t2 VALUES(1, 1); INSERT OR IGNORE INTO t2 VALUES(2, 2); INSERT OR IGNORE INTO t2 VALUES(3, 3); COMMIT; Error: FOREIGN KEY constraint failed I thought row id 1 & 3 simply would get ignored (due to the foreign key mismatch) when specifying INSERT OR IGNORE, but instead the whole transaction gets aborted. Is there any functionality availible to achieve what I want instead (get row 2,2 added to table t2)? Thanks! /D _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
