Insert gives foreign key constraint error when correct dependency breaks are listed

[ryan-riopelle]

Hi,

I have a database with multiple foreign key constraints between tables, and a table that has self dependencies. I was able to fix the self dependency issue by listing it as an excluded table. So when I run the condenser, the dp_dump works, but once it goes to insert records on a table at the destination inserts fail for records with foreign keys to the ignored table. I also tried listing these relationships in dependency breaks, but the problem still happens.

Does this tool adjust the foreign key constraints before inserting? Or does it assume foreign key constraint warnings are turned off prior to running for tables that are supposed to be ignored?

Does the order of two tables in dependency breaks tuple matter? I.e. {"fk_table": "table_1", "target_table": "table_2"} versus {"fk_table": "table_2", "target_table": "table_1"}? I tried both and also listed them together and neither of these solved the issue when writing/inserting records to related tables at the destination.

Also, this repo is not public. How can I push updates I made to include additional authentication parameters that I am sure would be useful to others.

[theaeolianmachine]

Hi Ryan! Thanks for your questions. A few answers:

• Condenser doesn't touch your foreign keys — it merely configures the traversal to ignore tables in the ignored tables section. Could you perhaps instead of excluding the table with the self reference add one of the columns to your list of dependency breaks?
• The order of dependency breaks does matter — the FK table is the one that contains the foreign key, the target table contains the primary key. If you have two tables pointing to each other with different FK and PK pairs, you should just pick one of them.
• I'm not sure what you mean by the repo is not public — do you mean your repo? This repo is certainly public, and you're free to fork it and send over a pull request with any work that you have.

Finally, it's notable that Tonic's commercial subsetter solves a lot of these problems without having to over think it — it handles variations of self dependencies, multiple ways of uniquely identifying a row in a table, automatic dependency breaks, and many other features. If you're interested in hearing about it, I'd be happy to point you towards its feature set and how it makes subsetting much easier than condenser's limited feature set.