Define master-detail relationships

SQL Edge® allows users to define the master-detail relationships between database objects, including tables, views, and synonyms representing tables or views. The user defined master-detail relationships combined with foreign key constraints are used by the “Relationship Table View” and “Relationship Tree View” to render related data. The feature is very useful when there are no foreign keys defined between related database objects, or the related database objects are in different databases.

To define master-detail relationships, select “Define Table Relationship…” menu option from “Settings” menu. The “Define Table Relationships” dialog will be displayed.

You can add new relationships, modify or delete any existing relationships.

Assume that there is a Customer table defined in one database, and PurchaseOrder, LineItem, OrderTaxInfo and Shipment tables defined in another database as shown below.

There are no foreign keys defined between any of these tables. You can define master-detail relationships for these tables in the “Define Table Relationships” dialog box.

Click the “Add” button to display the “New Table Relationship” wizard:

Select the master table database, and master table in the first page. Then, select the detail table database, and detail table in the second page:

Click the “Next” button to go to “Select columns” page:

Select related master detail table columns, and click the “Add” button to add them to related column list. Click “Finish” button when done. The new relationship will be added to the “Define Table Relationships” dialog box:

Similarly, you can define relationships between other tables.

Now, the “Relationship Table View” and “Relationship Tree View” will be able to render the related data in these tables. The following shows the “Relationship Table View” with the customer records in the master grid, and related purchase order records in the detail grid:

The following shows the “Relationship Tree View” with customer records as top-level tree nodes, and all their related detail records as child nodes: