Toad Data Studio 1.1 - User Guide

Diagram Relationships

Use the Database Diagram to view a high-level, visual representation of an object and those objects which may be related to it. This helps you easily identify relationships between tables via foreign keys. You can use the Database Diagram to:

• Understand the impact of changes.

• Identify join predicates when writing complex queries for reports.

• Understand the level of normalization of data when de-normalizing for performance or to save space.

• Understand entity relationships so you can better use the data in applications or reports.

Relationships Displayed Automatically

The Database Diagram supports tables, views, materialized views, and synonyms. However, existing relationships are automatically displayed only for tables and only when connected using a native database provider or certain ODBC connections, such as Access, PostgreSQL, and Netezza. For other object types and for some ODBC connections, relationships must be defined manually.

For most Business Intelligence and NoSQL connections, relationships must be defined manually.

• For more information about MongoDB relationships, see About MongoDB Data Sources.
• For more information about Salesforce relationships, see Create Salesforce SOQL Queries.

To diagram a relationship

1. Select Tools | Diagram.

2. Drag tables, views, or synonyms from the Object Explorer to the Database Diagram window. This method is useful if you want to add objects without setting the relationship types to display or the reference levels.

   Note: You can also right-click an object in the Object or Database Explorer and select Send To | Database Diagram.

3. Drag a column from one object to a column in a second object where you want to create a relationship.

   Notes:

   • This feature does not add new objects to the diagram if a relationship exists; it only displays relationships between objects already in the diagram.

   • A line between objects indicates an existing relationship.

   • A indicates the referencing table, and a indicates the referenced table.

4. To analyze a relationship, click the relationship line. See Analyze Joins for more information.

5. To reduce the number of columns that display in a large table, right-click the table and select Manage Hidden Columns. You can then hide any unnecessary columns to better understand the critical information.

6. To view the data type, nullable columns, and any comments for a selected object, right-click the object and select Show Column Details.
7. To view object details for a selected object, press F4, or double-click the object.
8. To add notes, right-click the Diagram pane, column, or object and select Notes. See Add Notes for more information.

Cross-Connection Relationships

You can use the Database Diagram tool to create relationships between two or more connections. The relationships you create are automatically saved and displayed the next time you diagram the same objects. Use the diagram, with the relationships you created, as a quick start when building cross-connection queries.

To diagram a cross-connection relationship

1. Select Tools | Database Diagram.
2. With the first connection active, double-click objects in the Object Explorer or drag objects to the Database Diagram window to add them to the diagram.

3. Switch to the second connection and add objects using the same procedure.

   Note: A blue banner along the right side of the Database Diagram window indicates that you are in Cross-Connection Mode.

4. In the diagram, drag a column from one object to a column in another object to create a relationship. The relationships are automatically saved in the Object Annotation Cache folder and displayed the next time you diagram the same objects.
5. To build a query using the tables and relationships you just diagrammed, right-click the diagram and select Send to Query Builder.
6. Save the diagram and use it as a template in future queries.

Database Diagram Toolbar

Use the following Database Diagram toolbar buttons to assist you in creating a database diagram.

Toolbar Button	Description
	Click to reorganize the objects in the diagram to make the relationships more visible.
	Click to resize each table in the diagram to show all columns.
	Select the depth of relationships you want the Database Diagram tool to use when it includes related objects in the diagram. Set to 0 to display only the selected object (the parent object).
	Click to view the object details for the object selected in the diagram (opens in a new pane).
	Click to open all tables and views for the current database, plus any referenced objects from other databases. To limit objects to the current database, select All Tables and Views from Current Database Only from the drop-down list.

Tips:

• When opening a previously saved Database Diagram file, you can right-click the Diagram pane and select Refresh to get the latest objects from the database, including added/removed columns and data type updates.

  An * in the title of the object indicates that the saved version differs from the database version.

• You can right-click an object to show or hide dependencies, column details, or individual columns within the object.
• To specify the type of relationships to display in the diagram and the number of levels of referential tables to include, specify options in Tools | Options | Database | Diagramming.
• Toad automatically saves a backup copy of any modified diagram file in the Application Data Directory to avoid losing your work. When you restart Toad after an unexpected close, Toad checks the timestamp on the backup file against the original file, and opens the file that has the latest timestamp. The document recovery option is in Tools | Options | Environment | General.
• To view the location of your Object Annotation Cache folder, go to Tools | Options | Database | Cache.

See also, Tutorial: Create a Database Diagram to Use as a Query Template.

Click here to view a video of this feature.