Working with self-referencing tables in master-detail browser

Last modified: June 26, 2023

This topic describes how to work with self-referencing tables in Master-Detail Browser.

A self-referencing table is a table that is a parent and dependent on the same referential constraint. That is in such tables a foreign key constraint can reference columns within the same table.

The example shows how to view data in a self-referencing table regarding available relations between columns using Master-Detail Browser.

Prerequisites

Be usre you have a self-referencing table. As an example, we will use the Employees table as a self-referencing table:

Viewing Data in a Self-Referencing Table

Task: it is required to view all employees regarding the levels of the company hierarchy using the data available in the sample Employees table.

Solution: Since the manager is also an employee, there is a relationship from the MGR column to the EmpNo column. The EmpNo column contains identification number of all employees, and the MGR column contains the identification number of the manager a person subordinates to (you see, the company president has (null) in the MGR column, as he doesn’t subordinate to anyone). Now, as you’ve understood the relationship between these columns, you can perform the following steps to solve the task specified above using dbForge Studio Master-Detail Browser:

1. On the Database menu, click Master-Detail Browser. The Master-Detail Browser document opens.

2. Drag-and-drop the Employees table you have created onto the designer surface.

3. Create a relationship between the MGR and EmpNo columns by selecting the MGR column and dragging it to the EmpNo column. An arrow indicating the relationship will be displayed on the diagram:

Note

There is no foreign key in this table, but if there is one and a relation between columns was established earlier, it’s displayed on the diagram after dropping the table onto it.

4. Click Browse Data.

5. In the Data view that opens, select the row with PRESIDENT specified in the Job column in the upper part of the view, and click + near each detail record in the lower part of the view to see the next level of company hierarchy and the employees belonging there: