Hello, We have an application that relies heavily on SQLite foreign key feature to ensure referential integrity of our database. Naturally we always enable foreign key enforcement immediately after connecting. However periodically, when we roll out new software (we have well over 300K installations around the world) we run schema upgrade scripts. To minimize the work an upgrade script needs to do and to limit possibly unwanted side effects those scripts are executed with foreign key enforcement turned off.
In a couple of cases due to minor bugs in an upgrade script and the fact foreign keys are not enforced during their operation the database was left in an inconsistent state with regards to the referential integrity. While we can fix the scripts (and we did) we cannot guarantee that those bugs won't happen in the future and thus are looking for an automatic way to verify referential integrity of an existing SQLite database that might have been modified while foreign key enforcement was not enabled. First I tried to roll out my own tool to verify referential integrity based on the information about foreign keys provided by the SQLite through the PRAGMA commands. However I found that it is impossible to unambiguously determine the order of columns in an implicitly specified compound parent key in all cases (short of trying to parse the SQL statement that created the parent table). However looking at SQLite source code it seems to be quite straightforward to add referential integrity verification to perhaps the PRAGMA check_integrity command. My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. Any thoughts on that? Thanks, - Levi _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
