> I got foreign key constraint failures
I don't know why one would work and one would fail, but usually, this
occurs when you insert a record which has foreign keys to another table,
but that table hasn't been imported yet. The workaround is usually to
ensure all the 'lookup' tables are done first, so when the main record is
inserted, the required record exists, or to turn off foreign key checks,
and only put them into the database once all the imports have completed.
On Tue, Aug 6, 2019 at 11:27 AM Olivier Mascia <o...@integral.be> wrote:
> On one database instance, a .dump command gives me (among many other
> lines) things like:
>
> INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
> while the output of .recover command gives me things this way:
>
> INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1,
> 11237795927160, 11868 );
>
> I'm wondering why these differences in the way to construct the
> instructions to rebuild a sound database instance. What are the (probably
> rightful) motivations?
>
> 1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping
> wanted, why not double quotes instead of single quotes?
> 2) Why do the insert statement prefer to name and repeat, ad nausea, the
> column names on each insert when, apparently, the shortcut syntax
> capitalizing on the known column order in the schema might seem much less
> verbose?
>
> On the real DB I quickly tested .recover on (with no reason, I have
> nothing to recover, just testing the feature) I had an issue while
> rebuilding a new DB from the script made by .recover. I got foreign key
> constraint failures (which I have not yet traced exactly).
>
> sqlite> .once system.sql
> sqlite> .recover
>
> sqlite3 recover.db
> sqlite> .read system.sql
> Error: near line 14658: FOREIGN KEY constraint failed
> Error: near line 14659: FOREIGN KEY constraint failed
> Error: near line 14660: FOREIGN KEY constraint failed
> sqlite> .q
>
> While doing the same kind of work around .dump worked nicely:
>
> sqlite> .once systemd.sql
> sqlite> .dump
>
> sqlite3 dump.db
> sqlite> .read systemd.sql
> sqlite> .q
>
> The source test db passes successfully those tests:
>
> sqlite> pragma integrity_check;
> integrity_check
> ok
> sqlite> pragma foreign_key_check;
> sqlite> .dbconfig
> enable_fkey on
> enable_trigger on
> fts3_tokenizer off
> load_extension on
> no_ckpt_on_close off
> enable_qpsg off
> trigger_eqp off
> reset_database off
> defensive off
> writable_schema off
> legacy_alter_table off
> dqs_dml off
> dqs_ddl off
>
> Again, I have no recovery to attempt for now. I was just exercising the
> .recover feature for learning, using a db I'm not suspecting of anything.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
>
> _______________________________________________
> 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
