Hello Dr. Hipp and all, Due to its implementation of foreign keys and their deferring, SQLite does not provide any useful information when a foreign key is violated; it gives a not very helpful "Foreign key constraint failed" message instead. This is a problem because an application developer cannot produce a proper message to a user when a FK is violated, for instance "You cannot delete X because it is connected to Ys" because they don't know and can't somehow deduce X and Y.
It occurred to me that this may be adequately worked around if foreign keys are deferred, using the "pragma foreign_key_check" command. The idea is that when a SQLITE_CONSTRAINT error occurs at commit, the application code can catch it and use the foreign_key_check pragma to get information about the FK violation(s) that caused the commit error so that a meaningful user message can be produced before doing a rollback. But this would impose a serious performance penalty if the check is "exhaustive" meaning that ALL records in the database are checked against ALL foreign key constraints. So my question is, is this pragma exhaustive or is it somehow optimised so that it does not always perform a full database scan -e.g. by means of some internal per-transaction FK violation counter or list? Because if it is optimised and thus fast enough then I suppose I can try to use it for the purpose I have described. TIA. -- Constantine Yannakopoulos _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
