On Fri, 24 May 2013 13:47:08 +0200 Clemens Ladisch <clem...@ladisch.de> wrote:
> Foreign key checks are not intended to prevent user errors but to > prevent programming errors. In other words, your program is > responsible for preventing the user from trying to delete some X that > is still referenced by some Y (by doing a check before deleting, or, > preferrably, by structuring the UI so that deleting such a X is not > possible). That is not true. In the first place, foreign keys aren't defined in terms of user/programming errors. They're defined, as you know, in terms of referential integrity. How that integrity came to be violated is of no concern to the DBMS. In the second place, the application is fundamentally unable to always prevent integrity violations. No matter how carefully the UI is structured, nothing prevents another process, quite outside the application's control, from deleting the very row that the application's insert will require. That is why the DBMS does the enforcement instead of the application. Yes, SQLite defaults to SERIALIZABLE isolation. The application has the option of SELECTing every affected FK in a transaction before commencing with the INSERT, thereby preventing their deletion by other processes. If all inserts are written that way, concurrency suffers and deadlocks are very difficult to avoid. Even if every RI violation is prevented, PK violations in general cannot be. Better constraint violation messages would be nice. Unfortunately, it's not easily done. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
