Error while using dbWriteTable to write a table with existing key

Question

I am running the code below

dbWriteTable(conn = mydb, "countries", cntr, overwrite = TRUE, row.names = FALSE)

and getting the error

Error: Cannot drop table 'countries' referenced by a foreign key constraint 'general_pop_estimates_ibfk_1' on table 'general_pop_estimates'. [3730]

I am wondering whether there's a work around on this

Answer 1

This type of error is common when another table (not countries) has a key relationship with one of the fields in countries. The default mechanism within dbWriteTable when overwrite=TRUE appears to be to drop the table completely, reconstruct its schema, and insert the data. Dropping the table breaks foreign key constraints.

You have a couple of options. Neither of which should be done blindly, as they are both irreversible (other than a restore-from-backup):

1. Find the foreign keys and somehow break the relationships, either by dropping the foreign keys or also dropping the dependent tables. Once the foreign key constraints no longer exist, this method of dropping countries and reconstructing should work without error (that is, use the same code as you tried before).

2. Keep the foreign key constraints, and just delete the rows from the current countries and insert the new data. This assumes that the schema of the table does not need to change (all types are the same, etc). Perhaps:

   dbExecute(conn = mydb, "delete from countries")
   dbWriteTable(conn = mydb, "countries", cntr, overwrite = FALSE, row.names = FALSE)
   # might need to add append=TRUE to that last call
   

   This is slightly untested, though -- depending on how the foreign key relationships cascade, the DBMS might complain that data in the other table must be removed first. In that case, you still need to find the foreign table and remove its data as well. (I did warn these options were destructive and irreversible, didn't I?)