Chat now with support
Chat with Support

Toad Data Modeler 7.2 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 Microsoft SQL Server 2019 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

Create Relationships

  1. Select a relationship type - click the appropriate relationship icon on the toolbar:

 Identifying relationship (also CTRL+R)

 Non-identifying relationship

   M:N relationship

  1. Move your mouse cursor over the work area. (The cursor changes its appearance.)
  2. Click the first entity (parent) and then the target entity (child).

TIP: Hide relationship names:

  1. Right-click the Workspace and select Workspace Format.
  2. In the Workspace Format dialog | General tab, select Hide Line Captions checkbox.

To create self-relationship for non-identifying relationship

  1. Click the Non-identifying relationship icon on the toolbar .
  2. Move your mouse cursor over the work area.
  3. Double-click the selected entity on the Workspace.

TIP: Before you create a self-relationship, select Settings | Options | Physical Model | Self Relation Attribute Name/Caption and define a name for propagated attributes there (e.g. via prefix, suffix, application variable.)

To add multiple relationships

  1. Press SHIFT and click the Relationship icon. See the blue frame in the icon now.
  2. Create as many relationships in you model as you need.
  3. Right-click the work area (or click the Relationship icon again) to turn this function off.

Edit Relationships

Double-click the relationship on the Workspace.

or

Edit the relationship in Model Explorer | Relationships folder | double-click the selected relationship (or right-click | Edit).

Example: The Relationship Properties dialog (Oracle 10g db)

TIP: When you point your mouse cursor at a relationship line, parent and child attributes are highlighted on the Workspace. You can set the color at: Settings | Options | Graphics | Colors area | Highlight Color.

Option Description

Caption

Logical name of relationship

Name

Physical name of relationship

About Naming Conventions

General Tab

Description

Relationship Type

Select this option to set the relationship as identifying or non-identifying. There are also several setting related to Parent and Child entities. The last checkbox gives you the option to create Index to Foreign Key in Child Entity.

Parent Key

Linking Method selection box. Available options: primary key, alternate key, unique item.

Select Parent Key for Relationship

Referential Integrity

Referential Integrity

Cardinality Area

Cardinality settings definition.

Toad Data Modelerallows you to set up synchronization of NotNull and Mandatory Parent.

Synchronization of Not Null and Mandatory Parent

Settings

Database dependent options (Oracle in our example).

Parent Entity Name and Child Entity Name

Names of entities that the relationship connects. Click the buttons next to these boxes to open the Entity Properties form of appropriate entities.

To Do Tab

On this tab, you can write some tasks on the selected relationship.   

Note: To see all To Do tasks, select Model | To Do.

Notes Tab

Tab for notes on particular relationship. The text will display in a pop-up box when you point your mouse cursor at the relationship name in ER diagram.

SQL Preview Tab

Click at the bottom of this tab to see the part of SQL code for the relationship.

Select the WordWrap checkbox to wrap the code on this tab.

Tip: To select this option by default, select Settings | Options | Physical Model | General tab | Word Wrap for SQL Preview.

Buttons:

- opens the Application Variables form

OK -  confirms all changes and closes the form

Cancel - cancels the changes you have made, and closes the form

Apply - confirms the changes (The form will remain opened for further edit.)

Help - opens the Help file

Referential Integrity

To define integrity rules of a relationship related to changing, adding or deleting a record inside parent or child entity

Set values in Referential Integrity group box.

Option Description

Parent Update

An event when the primary key of the record is going to be changed inside the parent entity. The rules are:

None

No rule.

Restrict

If the primary key is changed inside the parent entity record, and inside the child entity there are records allocated to the original parent record primary key,  the change will not be made and database will report an error.

Cascade

If the primary key is changed inside the parent entity record, and inside the child entity there are records allocated to the original parent record primary key, the database will change appropriate foreign keys inside the child entity.

Set NULL

If the primary key is changed inside the parent entity record, and inside the child entity there are records allocated to original parent record primary key, the database will set appropriate foreign keys inside the child entity to NULL.

Set Default

If the primary key is changed inside the parent entity record, and inside the child entity there are records allocated to the original parent record primary key, the database will set appropriate foreign keys inside the child entity to a default value.

Parent Delete

An event when a record inside the parent entity is being deleted. The rules are:

None

No rule.

Restrict

If a record inside the parent entity is being deleted, and inside the child entity there are records allocated to parent entity,  the command won't be executed.

Cascade

In case of record deletion inside the parent entity, the records inside the child entity will be deleted as well.

Set NULL

In case of record deletion inside the parent entity, the foreign keys inside the child entity will be set to NULL.

Set Default

In case of record deletion inside the parent entity, the foreign keys inside the child entity will be set to a default value.

TIP: Define default values for referential integrity type. Default Values

Edit Relationship Lines

Relationship lines consist of:

  • Lines
    • Solid lines represent identifying relationships.
    • Dashed lines represent non-identifying relationships.
  • Anchor Points - End points directly connected to entity boxes. You can alter them as you need.
  • Handle Points - Other points that can be added on relationship lines via CTRL key. Handle points allow you to select a part of relationship line to move or delete.

 

Move Lines, Handle Points and Anchor Points

To move part of line, handle point or anchor point

  1. Select a relationship line.
  2. Place mouse cursor over the line or point.
  3. Use drag and drop technique to change position of the line or point.

To move single anchor point and break a line

  1. Select a relationship line.
  2. Place mouse cursor over the anchor point.
  3. Use drag and drop technique to change position of the break point.

Column to Column Alignment

Lines can be rearranged manually and self-explanatory links from parent to child entities can be displayed in ER diagram.

Add Handle Points

To add handle point to a relationship line and break horizontal line:

  1. Click the selected relationship line.
  2. Press CTRL and click the line. Red cross icon will show up. Release CTRL key.
  3. Move your mouse cursor left or right and then move the highlighted part of the line up or down.


Result:

To break horizontal line using two handle points:

  1. Click the selected relationship line.
  2. Press CTRL and click the line. New red cross icon will show up.
  3. Do the same for the second position.
  4. Move your mouse cursor between the two handle points and then move the highlighted part of the line up or down.

Result:

Add Horizontal or Vertical Lines Only

To create horizontally or vertically straight lines

(Entities are beside or below each other.)

  1. Click the identifying or non-identifying relationship icon on the toolbar.
  2. Click parent entity and press and hold SHIFT key. Wherever you move your mouse cursor, only horizontal or vertical straight lines are offered.
  3. Move your mouse cursor to the desired position (over child entity)
  4. Click the target entity and release SHIFT key.

TIP: Using this method (via SHIFT key), you can lead the relationship as you need. E. g. you want to go around some entities in a particular way. If you just click the parent and child entity, the relationship will be created automatically and you will have to do some improvements later. To create your own track for the relationship:

  1. Click the identifying or non-identifying relationship icon on the toolbar.
  2. Click parent entity and press and hold SHIFT key. Wherever you move your mouse cursor, only horizontal or vertical straight lines are offered.
  3. Move your mouse cursor where you need to lead the line. Click the WS where you need to break it (still holding the SHIFT key). Make as many break points as you need.
  4. Finally, click the target entity and release SHIFT key.
 

Move, Hide, Find Relationship Names

To move the name of relationship on the Workspace

Right-click the selected relationship in particular place and select Move Caption Here, Move Caption to Parent or Move Caption to Child.

To hide relationship names on the Workspace

Click Hide Captions on the toolbar.

(Also right-click the Workspace | Workspace Format | General tab | select Hide Line Captions.)

To find the relationship line by its name on the Workspace

Click the relationship name (caption). The appropriate relationship line will be highlighted on the Workspace.

To find the relationship name (caption) by its line on the Workspace

Click the relationship line, its name (caption) will be highlighted in a frame.

 

Multiple Selection/Move of Relationships

Relationships are moved together with selected group of entities. (Select Objects

To edit the selected relationships and change their format at one jump, use SHIFT for multiple selection. Then right-click any selected relationship and select Edit or Format.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating