On Fri, Jan 6, 2017 at 10:30 PM, David Raymond <david.raym...@tomtom.com> wrote:
> http://www.sqlite.org/foreignkeys.html > > My version of the very basic basics anyway: > -Foreign key enforcement is turned on or off with a pragma and is a > connection-level setting, not a property of the database itself. > -The default enforcement is off unless a compile option is used. > -So unless you have full control over what's accessing a database then you > can't be sure clients are enforcing it. > -There's a foreign_keys_check pragma which will run an integrity check and > give you all the key violations. > -"deferrable initially deferred" is the only way to have a deferred key, > any other combo will be immediate. You can't change the schema for that at > will. > -You can force everything to be deferred if you use the defer_foreign_keys > pragma, but you can't force anything to immediate if it started deferred. > -Violation messages will often not tell you exactly which foreign key it > was that was broken, though if you name your constraints it can help. > -In keeping track of deferred foreign keys basically a tally of how many > keys were broken vs fixed is kept. If things don't come out even it can't > tell you where the problem record was, only that there was at least one > problem somewhere. > -A unique index has to be kept on the target (parent) field(s). Optionally > a regular one on the source (child) fields speeds up enforcement checks. Great summary David! I'd just add the new recently announced ".lint fkey-indexes" from the shell tool (cf http://sqlite.org/releaselog/3_16_2.html), which find instances of un-indexed FK child column(s). This is a classic in Oracle, where such unindexed FKs can easily lead to deadlocks. In SQLite, it's "just" of matter of performance, since locking is always at the db level anyway. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
