>
> 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)?
>
I have copied your case and no, transaction didn't get ignored.
sqlite> select * from t2;
id t1_id
---------- ----------
2 2
What is your version of sqlite?
OR IGNORE only tells sqlite to not uptade already existing records with same
PRIMARY KEY
So you either has to ignore SQLITE_CONSTRAINT errors or much better way is to
enable extended result codes
sqlite3_extended_result_codes(&db, 1);
and ignore only specific error from the set of SQLITE_CONSTRAINT errors:
SQLITE_CONSTRAINT_PRIMARYKEY
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
