Chat now with support
Chat with Support

Toad Data Modeler 6.3 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
About Physical Data Modeling Benefits of Physical Data Model Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects Model Properties Model Statistics
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Projects Reports Reverse Engineering Scripting and Customization SQL Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Supported Databases Connection Types by Database DB2 UDB v. 8 (LUW) DB2 v. 9 (LUW) DB2 v. 9.5 (LUW) DB2 v. 9.7 (LUW) DB2 v. 10.1 (LUW) DB2 v. 10.5 (LUW) DB2 z/OS v. 9 DB2 z/OS v.10 DB2 z/OS v. 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 Microsoft Access 2000/2002/2003 Microsoft Access 2007/2010 Microsoft Azure SQL Database Microsoft Azure SQL Database V12 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 Oracle 9i Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 PostgreSQL 8.1 PostgreSQL 8.2 PostgreSQL 8.3 PostgreSQL 8.4 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 SQLite 3.7 Sybase ASE 12.5 Sybase ASE 15 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
Legal Information

Script Generation Preparation

Features and Tools > DDL Script Generation > Script Generation Preparation

Before generating a DDL script, you might want to configure the following things:

Order of Generated Objects

Toad Data Modeler allows you to set the order of objects before SQL/DDL script generation.

Example: You want to generate Users before User Permissions to a table.

You can change order of the following objects:

  • Domains
  • Entities
  • Views
  • Dictionary types
  • Sequences
  • Stored procedures
  • Functions
  • Users
  • User data types

To set an order of objects for SQL/DDL script generation

  1. Go to Model Menu | Order of Generated Objects.
  2. Select an object, or an object type.
  1. Use to move your selection up/down by one step or use drag&drop to place your selection wherever you want.

Note: To display full names of objects, click on

  1. Switch to Extension tab for more options. Press F2 or left-click and hold any of the values to set it as desired.

You can configure when should be the listed object types generated.

To sort objects automatically according to their relationships

  1. Select Model | Order of Generated Objects to set the desired order of entities
    1. Select Move Parent Entity before Child Entity - lists each parent entity before their respective child entities or
    2. Select Move Child Entity after Parent Entity - lists all child entities after their parent entities

Note: When you set After CREATE TABLE Section value for an object, the object will not be shown in Entity SQL Preview.

Order of Generated Objects dialog is also accessible from DDL Script Generation dialog | Detail Settings tab| Edit.

Script Encoding

To set encoding for generated scripts

  1. Go to Settings Menu | Options | Model | Physical Model | SQL Generation Script tab | SQL Script section.
  2. Choose the preferred encoding from Encoding Used for SQL Scripts box.

How to Generate DDL Script

Features and Tools > DDL Script Generation > How to Generate DDL Script

This topic describes full configuration of a DDL script generation. In most cases, you will not need to configure all of the available settings.

Note: An Oracle 10g model is used in this topic. The dialogs and options available may vary depending on your model database platform and version.

Start by opening DDL Script Generation dialog using any of these methods:

  • Click the button on Model Toolbar
  • Go to Model Menu | Generate DDL Script | Run
  • Use shortcut - F9

Selecting Items for Generation

This essential part of script generation, you need to select items which should be generated.

Option Description
Location of SQL File Select the location where the generated script will be saved. You can also select an already existing script and check Append to File checkbox to append the generated code to the original file.
User/Schema Choose a user/schema from this box to only generate all of its assigned items.
Selection You can save/load your custom selections. To do that, simply select the objects you want to be generated from the Objects Grid and then click button to save your selection. If you will generate scripts in the future, you can load your selection or even make it default .
Bulk Selection

This section contains several buttons that help you select multiple objects quickly. You can:

  • Select All
  • Deselect All
  • Invert Selection
  • Auto Check (if enable, automatically checks/unchecks sub-items when you check/uncheck the parent item)

There is also a combobox which can be used to assign Extended Value to all items in Objects Grid at once.

Objects Grid In this grid you choose objects you want to generate by checking them in the Property Name column. And in the Extended Value column you can choose the SQL statement which should be used for the specific item in the final script (e.g. create, replace, drop).

Detailed Settings

Specific and database related settings can be found on this tab. Usually it is not necessary to change them in any way.

Referential Integrity

The main selection of objects for generation takes place in Select List. Referential integrity further specifies which relations are generated. It has the following options:

  • Always - relationships are always generated if they are included in Select List
  • If Child Table is Generated - relationship is generated only when its child table is included in Select List
  • If Parent Table is Generated - relationship is generated only when its parent table is included in Select List
  • If Child or Parent Table is Generated - relationship is generated only when child, parent or both are included in Select List
  • If Child and Parent Tables are Generated - relationship is generated only when both child and parent tables are included in Select List

Select List

If you want to generate only a set of specific model objects, you can do exactly that on Select List tab. By default, all objects that have Generate checkbox checked (e.g. Entity Properties) will be generated (unless you edited some other setting). If you uncheck the Generate by Property "Generate", you are able to select any objects you want.

Note: This tab is linked to the What to Generate tab | Objects Grid. If you uncheck an object type there, it you won't be able to select any of its objects in Select List tab.

Script Preview

You can preview the generated SQL script at any time. To do that, check the Show Preview checkbox on What to Generate tab. Then click the Generate button. Toad Data Modeler will offer you a preview of the script before you decide to save it. If you wouldn't have checked the Show Preview checkbox, the script would be saved first before you could preview it.

Note: Models are automatically verified before generating DDL script. To learn more, see Model Verification.


TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

Example of Generated Script

Features and Tools > DDL Script Generation > Example of Generated Script

Videorental model - Oracle 10g database

/*

Created: 15.3.2007

Modified: 6.12.2013

Project: Videorental Project

Model: Videorental

Company: Quest Software Inc.

Author: Jan Novak

Database: Oracle 10g

*/

-- Create user data types section -------------------------------------------------

CREATE TYPE SCOTT.Phone_List_Type

AS VARRAY(5) OF VARCHAR2(25)

/

CREATE TYPE SCOTT.Cust_Address_Type

AS OBJECT

( street_address VARCHAR2(40)

, postal_code VARCHAR2(10)

, city VARCHAR2(30)

, state_province VARCHAR2(10)

, country_id CHAR(2)

, phone Phone_List_Type

)

/

CREATE TYPE SCOTT.Price_Type

AS OBJECT (price NUMBER(10,2),

MEMBER FUNCTION total_price_VAT (vat number) RETURN NUMBER)

/

CREATE TYPE BODY SCOTT.Price_Type

IS

MEMBER FUNCTION total_price_VAT (vat NUMBER)

RETURN NUMBER IS

BEGIN

RETURN (price*((vat+100)/100));

END;

END;

/

-- Create sequences section -------------------------------------------------

CREATE SEQUENCE SCOTT.SeqExemplar

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOMINVALUE

NOCACHE

/

-- Create tables section -------------------------------------------------

-- Table SCOTT.T_CUSTOMER

CREATE TABLE SCOTT.T_CUSTOMER(

customer_id Integer NOT NULL,

name Varchar2(20 CHAR),

address SCOTT.Cust_Address_Type

)

/

-- Create indexes for table SCOTT.T_CUSTOMER

CREATE INDEX i_name ON SCOTT.T_CUSTOMER (name)

/

-- Add keys for table SCOTT.T_CUSTOMER

ALTER TABLE SCOTT.T_CUSTOMER ADD CONSTRAINT pk_T_CUSTOMER PRIMARY KEY (customer_id)

/

-- Table and Columns comments section

COMMENT ON COLUMN SCOTT.T_CUSTOMER.name IS 'Name column can contain First and Middle name. Surname must be in different column.'

/

-- Table SCOTT.T_GENRE

CREATE TABLE SCOTT.T_GENRE(

genre_id Integer NOT NULL,

name Varchar2(20 CHAR)

)

/

-- Add keys for table SCOTT.T_GENRE

ALTER TABLE SCOTT.T_GENRE ADD CONSTRAINT pk_T_GENRE PRIMARY KEY (genre_id)

/

-- Table SCOTT.T_MEDIUM

CREATE TABLE SCOTT.T_MEDIUM(

medium_id Integer NOT NULL,

medium_type Varchar2(20 BYTE)

)

/

-- Add keys for table SCOTT.T_MEDIUM

ALTER TABLE SCOTT.T_MEDIUM ADD CONSTRAINT pk_T_MEDIUM PRIMARY KEY (medium_id)

/

-- Table SCOTT.T_EXEMPLAR

CREATE TABLE SCOTT.T_EXEMPLAR(

exemplar_id Integer NOT NULL,

film_id Integer NOT NULL,

medium_id Integer NOT NULL,

price_per_day Integer

)

/

-- Add keys for table SCOTT.T_EXEMPLAR

ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT pk_T_EXEMPLAR PRIMARY KEY (exemplar_id)

/

ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT ak_exemplar_ppd UNIQUE (exemplar_id,price_per_day)

/

-- Table SCOTT.T_FILM

CREATE TABLE SCOTT.T_FILM(

film_id Integer NOT NULL,

title Varchar2(50 CHAR) NOT NULL,

director Varchar2(30 CHAR) NOT NULL,

production_company Varchar2(50 CHAR),

genre_id Integer NOT NULL,

min_age Integer,

film_ID_episodes Integer

)

/

-- Add keys for table SCOTT.T_FILM

ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT pk_T_FILM PRIMARY KEY (film_id)

/

ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT ak_title_director UNIQUE (title,director)

/

-- Table and Columns comments section

COMMENT ON COLUMN SCOTT.T_FILM.production_company IS 'Company name must contain also information about company type - LTD, Inc. and so on.'

/

-- Table SCOTT.T_BORROWING

CREATE TABLE SCOTT.T_BORROWING(

exemplar_id Integer NOT NULL,

customer_id Integer NOT NULL,

start_date Date DEFAULT sysdate,

end_date Date,

total_price SCOTT.Price_Type,

VAT Number(4,2) DEFAULT 19,

CONSTRAINT check_end_after_start CHECK ((end_date>start_date) or (end_date is null))

)

/

-- Add keys for table SCOTT.T_BORROWING

ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT pk_T_BORROWING PRIMARY KEY (exemplar_id)

/

-- Create triggers for table SCOTT.T_BORROWING

CREATE TRIGGER SCOTT.tri_BORROWING

BEFORE INSERT

ON SCOTT.T_BORROWING

FOR EACH ROW

declare price number(10,2);

begin

select price_per_day

into price

from T_EXEMPLAR

where T_EXEMPLAR.exemplar_id = :new.exemplar_id;

:new.total_price := Price_Type((:new.end_date-:new.start_date)*price);

end;

/

CREATE TRIGGER SCOTT.tru_BORROWING

BEFORE UPDATE

ON SCOTT.T_BORROWING

FOR EACH ROW

declare

price number(10,2);

total_price_old number(10,2);

end_d date;

start_d date;

begin

select e.price_per_day

into price

from T_EXEMPLAR e

where e.exemplar_id = :new.exemplar_id;

total_price_old := :new.total_price.price;

end_d := :new.end_date;

start_d := :new.start_date;

if (total_price_old is null) then

total_price_old := 0;

end if;

if ((end_d-start_d)*price != total_price_old) then

:new.total_price := Price_Type((end_d-start_d)*price);

end if;

end;

/

-- Table and Columns comments section

COMMENT ON TABLE SCOTT.T_BORROWING IS 'Borrowed items'

/

-- Table SCOTT.T_ORDER_RECORD

CREATE TABLE SCOTT.T_ORDER_RECORD(

customer_id Integer NOT NULL,

film_id Integer NOT NULL,

order_date Date

)

/

-- Add keys for table SCOTT.T_ORDER_RECORD

ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT pk_T_ORDER_RECORD PRIMARY KEY (customer_id,film_id)

/

-- Table and Columns comments section

COMMENT ON TABLE SCOTT.T_ORDER_RECORD IS 'All records are stored in list of records. It will be possible to book a movie.'

/

-- Table SCOTT.T_CUSTOMER_RATING

CREATE TABLE SCOTT.T_CUSTOMER_RATING(

title Varchar2(50 CHAR) NOT NULL,

director Varchar2(30 CHAR) NOT NULL,

rating Integer DEFAULT 3

CONSTRAINT check_rating CHECK (rating > 0)

)

/

-- Add keys for table SCOTT.T_CUSTOMER_RATING

ALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT pk_T_CUSTOMER_RATING PRIMARY KEY (title,director)

/

-- Table and Columns comments section

COMMENT ON TABLE SCOTT.T_CUSTOMER_RATING IS 'Movie ratings (by customers)'

/

-- Create procedures section -------------------------------------------------

CREATE PROCEDURE SCOTT.p_Customer_Has_Num_Film(cid IN integer, num OUT integer)

AS

BEGIN

SELECT count(*)

INTO num

FROM T_CUSTOMER c, T_BORROWING b, T_EXEMPLAR e, T_FILM f

WHERE c.customer_id=cid

and c.customer_id=b.customer_id

and b.exemplar_id=e.exemplar_id

and e.film_id=f.film_id;

END;

/

-- Create views section -------------------------------------------------

CREATE VIEW SCOTT.v_Customer_Has_Film AS

SELECT DISTINCT c.name, c.address.city AS city, f.title, f.director

FROM SCOTT.T_CUSTOMER c, SCOTT.T_BORROWING b, SCOTT.T_EXEMPLAR e, SCOTT.T_FILM f

WHERE c.customer_id=b.customer_id

and b.exemplar_id=e.exemplar_id

and e.film_id=f.film_id

/

-- Create functions section -------------------------------------------------

CREATE FUNCTION SCOTT.f_Customer_Has_Num_Film(cid IN integer)

RETURN integer

IS

sol integer;

BEGIN

p_Customer_Has_Num_Film(cid,sol);

RETURN (sol);

END;

/

-- Trigger for sequence SCOTT.SeqExemplar for column exemplar_id in table SCOTT.T_EXEMPLAR ---------

CREATE OR REPLACE TRIGGER SCOTT.ts_T_EXEMPLAR_SeqExemplar BEFORE INSERT

ON SCOTT.T_EXEMPLAR FOR EACH ROW

BEGIN

SELECT SCOTT.SeqExemplar.nextval INTO :new.exemplar_id FROM DUAL;

END;

/

CREATE OR REPLACE TRIGGER SCOTT.tsu_T_EXEMPLAR_SeqExemplar AFTER UPDATE OF exemplar_id

ON SCOTT.T_EXEMPLAR FOR EACH ROW

BEGIN

RAISE_APPLICATION_ERROR(-20010,'Cannot update column exemplar_id in table SCOTT.T_EXEMPLAR as it uses sequence.');

END;

/

-- Create relationships section -------------------------------------------------

ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT makes FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)

/

ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT is_related_to FOREIGN KEY (exemplar_id) REFERENCES SCOTT.T_EXEMPLAR (exemplar_id)

/

ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT places FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)

/

ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT is_available_on FOREIGN KEY (medium_id) REFERENCES SCOTT.T_MEDIUM (medium_id)

/

ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT is_required_by FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)

/

ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT is_of FOREIGN KEY (genre_id) REFERENCES SCOTT.T_GENRE (genre_id)

/

ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT has FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)

/

ALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT is_rated FOREIGN KEY (title, director) REFERENCES SCOTT.T_FILM (title, director)

/

ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT has_more_episodes FOREIGN KEY (film_ID_episodes) REFERENCES SCOTT.T_FILM (film_id)

/

-- Grant permissions section -------------------------------------------------

insert into T_GENRE values (1,'crime')

/

insert into T_GENRE values (2,'western')

/

insert into T_GENRE values (3,'drama')

/

insert into T_GENRE values (4,'biography')

/

insert into T_GENRE values (5,'comedy')

/

insert into T_MEDIUM values (1,'CD - DivX')

/

insert into T_MEDIUM values (2,'CD - VideoCD')

/

insert into T_MEDIUM values (3,'DVD')

/

insert into T_MEDIUM values (4,'Videotape')

/

insert into T_FILM values (1,'The Shawshank Redemption','Frank Darabont','Castle Rock Entertainment',1,15, null)

/

insert into T_FILM values (2,'The Godfather','Francis Ford Coppola','Paramount Pictures',1,15,null)

/

insert into T_FILM values (3,'The Godfather: Part II','Francis Ford Coppola','Paramount Pictures',1,15,2)

/

insert into T_FILM values (4,'The Good, the Bad and the Ugly','Sergio Leone','PEA',2,15,null)

/

insert into T_FILM values (5,'Pulp Fiction','Quentin Tarantino','Miramax Films',1,18,null)

/

insert into T_FILM values (6,'12 Angry Men','Sidney Lumet','Orion-Nova Productions',3,15,null)

/

insert into T_FILM values (7,'Schindler''s List','Steven Spielberg','Universal Pictures',4,15,null)

/

insert into T_FILM values (8,'One Flew Over the Cuckoo''s Nest','Milos Forman','Fantasy Films',3,15,null)

/

insert into T_EXEMPLAR values (1,1,3,3)

/

insert into T_EXEMPLAR values (2,1,3,3)

/

insert into T_EXEMPLAR values (3,1,3,3)

/

insert into T_EXEMPLAR values (4,1,4,2)

/

insert into T_EXEMPLAR values (5,1,4,2)

/

insert into T_EXEMPLAR values (6,2,3,3)

/

insert into T_EXEMPLAR values (7,2,4,2)

/

insert into T_EXEMPLAR values (8,3,3,3)

/

insert into T_EXEMPLAR values (9,4,4,2)

/

insert into T_EXEMPLAR values (10,5,3,3)

/

insert into T_EXEMPLAR values (11,6,4,2)

/

insert into T_EXEMPLAR values (12,6,4,2)

/

insert into T_EXEMPLAR values (13,7,4,2)

/

insert into T_CUSTOMER values (1,'Audrey',Cust_Address_Type('92A Campton Avenue','60021','Fox River Grove',null,'us',null))

/

insert into T_CUSTOMER values (2,'Simon',Cust_Address_Type('12 Hillpeak Street','60006','Arlington Heights',null,'ca',null))

/

insert into T_CUSTOMER values (3,'Dave',Cust_Address_Type('31D South Avenue','60001','Alden',null,'us',Phone_List_Type('541 123 456')))

/

insert into T_CUSTOMER values (4,'Chris',Cust_Address_Type('5 Quiet Street','60061','Vernon Hills',null,'us',Phone_List_Type('541 123 456','596 815 641')))

/

insert into T_CUSTOMER values (5,'Elen',Cust_Address_Type('47 My Avenue','60083','Beach Park','Illinois','us',null))

/

insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (11,1,to_date('01-01-2010','DD-MM-YYYY'),to_date('03-01-2010','DD-MM-YYYY'))

/

insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (12,2,to_date('02-01-2010','DD-MM-YYYY'),to_date('10-01-2010','DD-MM-YYYY'))

/

insert into T_BORROWING (exemplar_id,customer_id,start_date) values (1,2,to_date('06-01-2010','DD-MM-YYYY'))

/

insert into T_BORROWING (exemplar_id,customer_id,start_date) values (6,4,to_date('07-01-2010','DD-MM-YYYY'))

/

insert into T_BORROWING (exemplar_id,customer_id,start_date) values (7,3,to_date('07-01-2010','DD-MM-YYYY'))

/

insert into T_BORROWING (exemplar_id,customer_id) values (9,3)

/

insert into T_ORDER_RECORD values (1,2,to_date('01-01-2010','DD-MM-YYYY'))

/

insert into T_ORDER_RECORD values (1,1,to_date('01-01-2010','DD-MM-YYYY'))

/

insert into T_ORDER_RECORD values (2,2,to_date('03-01-2010','DD-MM-YYYY'))

/

insert into T_ORDER_RECORD values (3,3,to_date('06-01-2010','DD-MM-YYYY'))

/

Graphics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating