You observe no violation when VIOLATION is 0 because there is no referential integrity violation to report ...
However, you are correct that when inserting data the as shown in your code (where there is a referential integrity violation) the insertion is much slower after the violation occurs, presumably because on each subsequent insert into table a it is checking whether the deferred violation has been resolved yet. Presently this is how the referential integrity checking works in SQLite3. Note that if you change your reference to table b rather than table a and you are operating only on table a this does not occur since there is no operation on table b which might resolve the violation. It also makes the extra insert a violation when you define VIOLATION as 0 ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Yuri >Sent: Wednesday, 1 August, 2018 16:57 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Violated failed foreign key constraint delays the >rest of transaction ; Some foreign key violations don't trigger the >error at all > >The attached testcase injects the foreign key violation into a long >transaction. This makes the remainder of the transaction much slower, >even though the foreign key is deferred, and should only be checked >in >the end of the transaction. > > >While working on this testcase, I found that sometimes the foreign >key >violation doesn't trigger the error at all. Please change VIOLATION >to >0, and observe that there is no failure now, though it should be. > > >sqlite3-3.24.0 on FreeBSD 11.2 > > >Yuri > > > >---testcase--- > >#include <sqlite3.h> >#include <stdio.h> >#include <stdlib.h> >#include <time.h> > >void doSql(sqlite3 *db, const char *sql) { > char *err_msg = 0; > int rc = sqlite3_exec(db, sql, 0, 0, &err_msg); > if (rc != SQLITE_OK ) { > fprintf(stderr, "SQL error: %s\n", err_msg); > sqlite3_free(err_msg); > sqlite3_close(db); > exit(1); > } >} > >#define NROWS 100000 >#define VIOLATION 1000000 > >int main(void) { > sqlite3 *db; > > char s[512]; > > int rc = sqlite3_open(":memory:", &db); > if (rc != SQLITE_OK) { > fprintf(stderr, "Cannot open database: %s\n", >sqlite3_errmsg(db)); > sqlite3_close(db); > return 1; > } > > doSql(db, "PRAGMA foreign_keys = ON;"); > > printf("creating B ...\n"); > doSql(db, "create table b (id int PRIMARY KEY, name text)"); > > printf("populating B ...\n"); > for (int i = 0; i < NROWS; i++) { > sprintf(s, "insert into b values(%d, 'The name field for %d')", >i, i); > doSql(db, s); > } > > printf("creating A ...\n"); > doSql(db, "create table a (id int PRIMARY KEY, name text, aid int >not >null, FOREIGN KEY(aid) REFERENCES a(id))"); > > printf("populating A ...\n"); > doSql(db, "BEGIN TRANSACTION;"); > doSql(db, "PRAGMA defer_foreign_keys=ON;"); > for (int i = 0; i < NROWS; i++) { > if (i % 1000 == 0) > printf("...row#%d... (time=%ld)\n", i, time(0)); > sprintf(s, "insert into a values(%d, 'The name field for %d', >%d)", >i, i, i); > doSql(db, s); > // introfuce the FK violation > if (i == NROWS/4) { > sprintf(s, "insert into a values(%d, 'The name field for %d', >%d)", NROWS+i, i, NROWS+i+VIOLATION); > doSql(db, s); > } > } > doSql(db, "COMMIT TRANSACTION;"); > > sqlite3_close(db); > > return 0; >} > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
