I hope I can get help on this. I've read all the documentation and I can't figure this one out.

1. I have an existing SQLite database with multiple tables that has FK relationships defined (and enforced)(and some of the tables not only have FKs to other tables, but also to other rows in the same table).

2. I want to (frequently) "merge in" other "new" data from another source that follows the same schema, such that the new data is made up of multiple rows across multiple tables with FKs that relate all of the "new" data together in complex ways. (To clarify, the "new" data has FKs to other rows in the "new" data.)

3. For the new data to be merged in from (2), because the FKs can reference the same table (and other tables) in complex ways, it's basically impossible to hand-write a pre-determined insert order of the tables (and table rows) such that the FKs always exist before they are added to the (existing) SQLite database.

In Postgres, it was possible to "reserve" new PK IDs for all tables before inserting any of the new data into the database (by selecting for each table a set of "new" row IDs using SELECT nextval('tablename_id_seq') FROM generate_series(1,the number of rows to insert into this table) and then rewriting the FK values in code before inserting them into the database, so that by the time the new rows where written into the existing database, all of the FKs had been updated (safely and reliably).

I can't figure out how to solve this in SQLite.

I hope that is clear what I'm asking.

Turn off foreign keys using pragma foreign_keys=0 for the connection on which you wish to 'write' without enforcing foreign key constraints.

On that connection BEGIN IMMEDIATE to start an write transaction (and stop anyone else from changing the database).

Insert all your data with your fixes, same as Postgress. If there is a problem in implementing that process, what is it?

Check that the database is consistent using pragma foreign_key_check

If the database is not consistent, ROLLBACK the transaction. Otherwise COMMIT the transaction.

Turn on foreign key enforcement using pragma foreign_keys=1

Next time you need to do this start at the top again.

Thanks! Two questions:

Turn off foreign keys using pragma foreign_keys=0 for the connection on which you wish to 'write' without enforcing foreign key constraints.

If I'm using a connection pool from my code, can I use the pragma foreign_keys=0 for the connection I get from the pool, and then when I'm done, the connection will be returned to the pool without a problem (even if there is a problem in the connection and I cannot set the "pragma foreign_keys=1"? (I don't want to mess up future pool connections)

Insert all your data with your fixes, same as Postgress. If there is a problem in implementing that process, what is it?

I think this is where the problem is. I need to set the FK columns for each row, but I don't know what IDs to use. In Postgres, I could "pre-allocate" all of the IDs and then update the data to insert the correct FK IDs before adding them to the database, but I'm not sure how to do that in SQLite without inserting all of the rows into each table, and then afterwards updating all of the FK values once I know what all of the IDs are (using "RETURNING id", for instance), which means I have to write each row twice -- once to get it into SQLite and to generate an ID, and the second time to update the FK columns after I know all of the IDs.

If I'm using a connection pool from my code, can I use the pragma foreign_keys=0 for the connection I get from the pool, and then when I'm done, the connection will be returned to the pool without a problem (even if there is a problem in the connection and I cannot set the "pragma foreign_keys=1"? (I don't want to mess up future pool connections)

That would depend if the connection pool resets the connection to a known state when it is returned to the pool (before being handed out again). If not, then you will need to put things back as they were before returning the connection to the pool.

I think this is where the problem is. I need to set the FK columns for each row, but I don't know what IDs to use. In Postgres, I could "pre-allocate" all of the IDs and then update the data to insert the correct FK IDs before adding them to the database, but I'm not sure how to do that in SQLite without inserting all of the rows into each table, and then afterwards updating all of the FK values once I know what all of the IDs are (using "RETURNING id", for instance), which means I have to write each row twice -- once to get it into SQLite and to generate an ID, and the second time to update the FK columns after I know all of the IDs.

You can do the same thing. You can find out the start record number that will be inserted into table x with select coalesce(max(rowid),0)+1 from x; provided that the table x does not use autoincrement and assuming that the primary key is the rowid and that these are what are referenced by the foreign key constraints. You then use this as the new id for the next record inserted in that table.

This makes total sense. Thank you!!!