User-defined relationships

Within AQT, you can define relationships between tables. This is used in:

• the Query Builder. Once a relationship is defined between tables, the tables can be joined by simply clicking on the Add Related Tables button.
• the Link to Related Table function of the Data Display window. This allows you to navigate between tables that have a known relationship.

In many cases, the relationships between tables will have been defined to the database as a Foreign Key relationship. In these cases, AQT will detect these relationships(*). However there are a number of circumstances where tables are related, yet this relationship is not defined to the database:

• Your system designers have chosen not to implement the relationship (for performance or other reasons).
• The relationship is between system tables (foreign-key relationships are not defined between these).
• The database does not support foreign key relationships.
• You are joining views or synonyms – foreign key relationships cannot be defined between these objects.

In these cases you can define the relationship as a User Defined Relationship, or UDR. Once you have done this, the relationship between these tables becomes a "known" relationship in the Query Builder and Data Display windows.

(*) Note that if you have set Options > Table Information > Get Related Table information from User Defined Relationships only, AQT will get the related table information from the UDRs only, and will not query the database.

To define a UDR

This can be done as follows:

• in the Query Builder, join the tables as per normal (by dragging between columns). Once you have done this, right click the join and select Remember Join. Alternatively you can edit the join details and click on the Remember button. You will get the message "Join Details Saved".
• in the Query Builder, click on the Add Another Table button, then click on the Select Known Relationships tab. This will show you all the known-relationships involving the current query tables (excluding those relationships already in the query). This shows both database-defined-relationships, as well as user-defined-relationships – the Source column shows you where the relationship comes from. Click on View User Defined Relationships to go to the User Defined Relationships window from where you can add / delete / change your UDR entries.
• in the Data Display window, click on Link > Edit list of Related Tables. This will take you to the User Defined Relationships window.
• in the Database Explorer window, click on Tools > Edit List of Related Tables. This will take you to the User Defined Relationships window.

Using the User Defined Relationships window

This window shows you all the existing UDRs for your current database. You can:

• edit existing entries by double-clicking an entry, or clicking on the Edit button
• delete entries by selecting them, then clicking on the Delete button
• add a new entry by clicking on the Add button.

When editing an entry:

• select a table by clicking in the Tables column, then clicking on the three-dots.

  specify the join columns by clicking in the Cols column, then clicking on the three-dots. This will take you to the Joins window from where you can specify the join between the tables.

• you can specify a Join Type. This is only used for the Query Builder and gives the type of the join when it is added to the query.
• you can specify a Description for the join. This is useful when you have many joins for a table, and wish to have a meaningful description of them. When using the Link to Related Tables function, the description is shown on the right of the Related Tables dropdown box.
• when defining a UDR, you may also wish to specify whether the relationship is a Foreign Key relationship. This is explained in more detail in Understanding Foreign Keys.

Generic Relationships

You can specify Generic relationships. These are useful if you have the same set of tables (with the same relationships) defined in a number of different schemas. By defining the relationship as generic, you need define each relationship only once - it will then apply to all schemas.

Example: suppose you defined a generic relationship between Schema1.TableA and Schema1.TableB. This would then imply:

• a relationship between Schema2.TableA and Schema2.TableB
• a relationship between Schema3.TableA and Schema3.TableB
• it does not imply relationships between schemas. So Schema1.TableA and Schema2.TableB are not related.

UDR File Location

The information on the UDRs is held in a file. There is a separate UDR file for each database. The UDR filename is database.udr (for instance aqtdemo.udr). The directory where the UDR files are stored is the Default Directory. The file name is given in the caption on the User Defined Relationships window.

You can manipulate these files in the same way as any other files. For instance, if you have a UDR file set up you can copy it to another person's PC so that they will be able to use the UDR definitions you have set up.

Similarly, if you have multiple similar databases (for instance, development and production). You can set up the UDRs for one of the databases, then copy the contents into UDR files for the other databases.