It is correct. On the chance that you happen to have compiled your version of SQLite with Foreign Key enforcement turned on by default instead of off; or, a later versions decides to change the default to on rather than off; when you load a dump file you need to have that foreign key enforcement off in order to be able to load the database. This is because the tables and data are dumped in random order, not in hierarchical order (parents of parents then their children then their children and so on and so forth) or mayhaps there are self-referential or referential loops which cannot be resolved without turning off foreign key enforcement while loading the database "in bulk" rather than by following the application business logic processing to only add records the would meet referential constraints.
The batch process cannot turn it back on for you because it does not really know (keep track of) the state around the "load" operation. In other words, if you need FK enforcement on, use the pragma to turn it on. If you need it off, use the pragma to turn it off. It is unwise to depend on the default always being the same for everyone everywhere for all time. In the present circumstance the default is off. This is because at one time there was no FK enforcement so no application turned FK enforcement off. If you changed the default, then all those applications might stop working (because the DDL syntax was supported, but it was just ignored). --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Niall O'Reilly >Sent: Thursday, 8 January, 2015 06:39 >To: General Discussion of SQLite Database >Subject: [sqlite] Should .dump preserve foreign_keys pragma? > > > Hello. > > What follows puzzles me. Either there's something I don't > understand, or something is wrong. > >dhcp-179(niall)7: sqlite3 >SQLite version 3.8.5 2014-08-15 22:37:57 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> PRAGMA foreign_keys=on; >sqlite> PRAGMA foreign_keys; >1 >sqlite> .dump >PRAGMA foreign_keys=OFF; >BEGIN TRANSACTION; >COMMIT; >sqlite> ^D >dhcp-179(niall)8: > > I'ld have expected the foreign_keys pragma setting to have been > preserved. > > The version shown is currently bundled with Apple's OSX Yosemite. > I've checked subsequent release history for changes and not found > any of relevance. > > Thanks in anticipation for any enlightenment. > > Best regards, > Niall O'Reilly > >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
