On 12 Dec 2012, at 7:29pm, Levi Haskell (BLOOMBERG/ 731 LEXIN) <lhask...@bloomberg.net> wrote:
> 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. For checking a database to see whether your FOREIGN KEYs have already been violated, it's possible to check an entire relation with a single SELECT ... JOIN ... ON command. Just check to see if there are any rows in the related table that have an id of NULL. I think that's how I did it. So you can check an entire database which has six FOREIGN KEY relations just by using six SELECTs and seeing if any return more than zero rows. Since you must already have appropriate indices for the keys to work the SELECTs should run quickly. > 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). I see what you mean. You have to have the original programmer write your checking statements, you can't generated them automatically. I'm not fond of writing my own parsing code. Maybe SQLite4 will provide better PRAGMAs for this stuff. Other than that, it might help to know there's a compilation directive which sets FOREIGN KEY enforcement on by default: http://www.sqlite.org/compile.html and search for 'SQLITE_DEFAULT_FOREIGN_KEYS'. With this set there's no need to be so sure your own code uses the PRAGMA every time you open a connection. So if your compilation environment will let you set a C directive you can use this ... though only on your own app. It won't help if someone opens your database using the shell tool or something. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
