Good stuff Keith. One to archive.
________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Tuesday, November 28, 2017 4:02:35 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key help And of course in the command line shell you can and should use .lint fkey-indexes to let you know if you are missing any indexes required for efficient foreign-key enforcement operations. It will report missing indexes on the PARENT (table/columns referred to) and on CHILDREN (tables/columns referred from) since you need these indexes in order for foreign key enforcement to work without having to resort to table scans of the entire parent/child tables(s) on each row of an insert/update/delete operation affecting either a parent or child foreign key column. If you do not have the necessary indexes defined for these relationships you might erroneously think that referential integrity enforcement is excessively expensive, and continue to use "insert/update/delete with prayers" to maintain referential integrity rather than have the database engine do it for you. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >On 28 Nov 2017, at 3:26pm, x <tam118...@hotmail.com> wrote: > >> If I have foreign keys in place but always have foreign_keys = OFF >then one day start SQLite with foreign_keys = ON what happens? Does >SQLite suddenly check all foreign keys and report / delete violations >or does it leave everything as is and just enforce foreign keys from >that point on? > >No. There is no automatic check. If you have broken a foreign key >constraint while "foreign_keys = OFF" it may only be discovered some >time in the future, or it may live on for as long as the database is >used. > >However, you can force SQLite to check all foreign keys any time you >want using > ><https://sqlite.org/pragma.html#pragma_foreign_key_check> > > PRAGMA foreign_key_check > >If it returns no rows, then there are no problems. This check will >work even while "foreign_keys = OFF", so you can check it before you >turn that PRAGMA back on. > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
