When you add a database to the data dictionary, Stonefield Query tries to read information about the relationships between tables from the data source. If the data source doesn't contain any relationship information, either because the database engine doesn't support that information or because the database designer didn't define any relationships in the database, you have to define the relationships between the tables using Stonefield Query Studio. That can be somewhat tedious, especially if there are a lot of tables. The Find Possible Relations process, accessed from the Find Possible Relations function in the File menu and Find Relations for Table in the Objects and shortcut menus, can help.

When you invoke this function, Studio scans through the data dictionary for every table (or just the selected one if you choose Find Relations for Table), looking at each field to see if there's another table in the data dictionary with that same field name. Because checking every field in a table is rarely necessary (for example, it's unlikely the Address field in a Customers table is related to anything), Studio asks you to enter something to search for in the field name. For example, many developers use "ID" in the name (such as "CustomerID" or "IDCust") to signify that the field is a primary or foreign key. In that case, enter "ID" or "id" (the case isn't important; Studio checks for names without regard to case) to search for any field containing "ID" in its name. If you have more than one value, separate them with commas (for example, "id, key").

If Studio finds a table containing a field with the same name, it tests to see whether there's a relationship between those tables by doing a query similar to SELECT ... FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.FIELD = TABLE2.FIELD. If it finds any matching records, it creates a possible relation for those two tables. When it's finished, it displays a dialog in which you can review the possible relations.

Select a specific relation in the grid and then choose one of three options:

  • Swap Parent & Child: if the table Studio thinks is the parent should really be the child, click this button to swap the parent and child settings.

  • Accept: click this button to add the possible relation to the data dictionary and remove it from the review list.

  • Discard: click this button to remove the relation from the review list.

You don't have to review all the possible relations at once; use the Review Possible Relations function in the File menu to review them at any time.