The other day, I tried to delete a couple of records from our database. Not a hard task you may think and I would agree with you. However, this database table had quite a few incoming foreign key constraints that made it impossible to delete records from it without deleting records in other tables first. Not knowing the relations between tables made it impossible to decide which record to delete in which order. In search for a solution I found Jailer. In this article, I want to show how to analyze dependencies between records in relational databases with Jailer (version 4.3.2).

Jailer is open source and doesn’t need to be installed. Just extract it somewhere and setup a database connection after starting Jailer.exe. Jailer will analyze your database, which can take a few minutes. In that process, Jailer creates a couple of temporary tables without touching the existing ones.

There is a description in the FAQs to setup these tables in a different database schema. We didn’t have trouble with these tables in our schema.

To analyze the data in your tables, start the JailerDataBrowser. You can either start the jailerDataBrowser.exe directly or in Jailer Tools -> DataBrowser.

After choosing a database connection for Jailer to use, it shows a list of tables.

Open one of them by selecting it and pressing “open table”. Jailer will show you the content of that table and an interface to filter and limit the records you see.

By right-clicking on of the records, you can browse the children of this record, i.e. the records in other tables that have a dependency on this record.

By choosing these children, the corresponding tables will appear and you can repeat the process there. This way, you can build up the whole dependency graph of one specific table record.

Notice that in the context menu, there are sometimes gray table names in the children-list. These are the tables some records of the analyzed table have dependencies to, but not the current record. It’s likely to be a null-able dependency. Also quite interesting is that the analyzes of dependencies works from children to parents. Just follow the “parents”- entry in the context menu.

TL;DR

Jailer can analyze dependencies of relational data. You can use Jailer to explore and understand a database or to find dependent records.