Skip to main content

Restore SQL Backups in DB with Foreign Keys

When restoring backups for an SQL database with Foreign Key references, you may see a response like;

danger

cannot truncate a table referenced in a foreign key constraint

This is because our SQL restore function will first empty your table and then import the backed up data, and when you try to delete the contents of a table whose rows are connected to other tables, you will get the foreign key error.

For now we are working on intuitive ways to solve this but you can follow these steps for a temporary work around;

How to restore a backup in this situation​

For this guide we will use 3 sample tables, jobs, locations and applications, where locations is referenced by both jobs and applications, and jobs is referenced by applications.

  1. First step to restoring data for all your tables is to locate the database backups, the path should be visible in the list of database dumps.

  2. Delete the contents of each table in ascending order of reference count, in our sample case applications is referenced the least (0 - not referenced by any table) and locations is referenced the most (2 - referenced by jobs and aplications). So delete the contents of applications, jobs and finally locations. You can do this with a command or by ticking rows for multiple delete on the table UI.

  3. Now restore each table using the import feature on the table page. This should be done in reverse order of Step 2 above, Import locations first, then jobs and finally applications.

By following a similar approach on your database you should be able to backup and restore seamlessly while we work on a more permanent solution.