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.

The User Defined Relationships window

Notes

• Aliases are used to easily identify a relationship. You can put fuller information in the Description.
• you may also wish to specify whether the relationship is a Foreign Key relationship. This is explained in more detail in Understanding Foreign Keys.
• use the Where column to specify an additional Where clause for the join. This is used in cases where your join condition is (for example)

  tab1.keycol = tab2.keycol and tab1.col2 = 'X'

  in this case tab1.keycol = tab2.keycol is the join condition (as it involves both tables) and tab1.col2 = 'X' is an addition Where clause on the join.

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.