Import configuration and relational data

Question

Suppose that a user has multiple items. Each item would have complex configuration - multiple tables with relationships. Each configuration table would have an id and a item_id which is a FK to items table.

My question is given that we have a relational database and we export all tables to a text format (csv,json, etc) how can we reimport this data for another item as cloned? Importing just data would be easy since a new id will be generated for every row. However creating a id means that foreign keys would be invalid.

The only way I figured to solve this issue is to store data first(without FKs) for all tables and then knowing each table new PK update foreign keys. I could use a dictionary to map old ids with new ones.

Is there a cleaner way to achieve something like that?

Answer 1

A better solution might be to use the "insert .. select" form of the insert statement. If your items have an auto-incrementing [primary] key, then inserting a record without specifying that column will cause the database to allocate a new value, which you can then retrieve using a DBMS-specific function.

select * from month_days ; +----+---------+------+ | ID | Month | Days | +----+---------+------+ | 1 | January | 31 | +----+---------+------+ insert into month_days ( Month, Days ) select 'March', Days from month_days ; select * from month_days ; +----+---------+------+ | ID | Month | Days | +----+---------+------+ | 1 | January | 31 | | 2 | March | 31 | +----+---------+------+ 

Answer 2

Some databases like SQL Server have a means to disable constraint testing for just such purposes. I think it goes without saying that you should be sure you know what you're doing in such mode. To play it safe, you would disable the individual rules rather than disable all constraints and in the case of failure, rollback, and then enable constraints again.