On 12 Dec 2012, at 9:30pm, "Levi Haskell (BLOOMBERG/ 731 LEXIN)" <lhask...@bloomberg.net> wrote:
> I see your point but another way to look at it is that PRAGMA > foreign_keys=0/1 changes the definition of what constitutes a > legal/consistent state of the database, thus it makes sense that behavior of > check_integrity PRAGMA would change accordingly. So your argument is that with 'PRAGMA foreign_keys' off, the integrity check wouldn't worry about foreign keys. I can get behind that. On the other hand, the argument that PRAGMA integrity_check should check constraints (including but not limited to UNIQUE) is far stronger. SQLite stops you from messing these up: if you try to define a constraint when data violating it already exists, SQLite issues an error message. So any database containing violations is by definition corrupt. There are a few ways you can violate FOREIGN KEY requirements. Not only can you turn the PRAGMA off, but SQLite allows you to declare a FOREIGN KEY relationship when no appropriate index exists. I would prefer it to either issue an error result or to generate its own key when this is done, but instead it waits until something that uses the key to notice that no index exists, and then issues a puzzling error message. Given the above, I would accept that checking FOREIGN KEYS shouldn't be in PRAGMA integrity_check by default. Possibly all this can be fixed in SQLite4, which shouldn't allow a FOREIGN KEY to be declared unless the data and indexes in the database are everything it needs. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
