Database Objects' DDL - Database Tour Documentation

What is DDL

DDL is a subset of SQL and means Data Definition Language. It is used to define and modify database objects, for example, create, change, or delete tables, indexes, views, procedures etc.

In Database Tour, DDL term is applied to SQL code needed to create selected database object and (optionally) its child objects, with (optionally) the corresponding user permissions.

Here is an example of DDL code for creating a table in PostgreSQL database:

-- Create table
CREATE TABLE detail.contracts (
  id serial not null,
  source_type_id smallint not null,
  state_id smallint not null,
  type_id smallint not null,
  client_id integer not null,
  contract_date date not null,
  contract_number varchar(25),
  close_date date,
  ts timestamp without time zone default now()
);
-- Add object comments
COMMENT ON table detail.contracts is 'Contracts';
-- Create primary key
ALTER TABLE detail.contracts
  ADD CONSTRAINT contracts_pkey
  PRIMARY KEY (id);
-- Create foreign keys
ALTER TABLE detail.contracts
  ADD CONSTRAINT fk_contracts_client
  FOREIGN KEY (client_id) REFERENCES detail.client (id) ON DELETE no action ON UPDATE no action NOT DEFERRABLE;
ALTER TABLE detail.contracts
  ADD CONSTRAINT fk_contracts_contract_state
  FOREIGN KEY (state_id) REFERENCES detail.dic_contract_state (id) ON DELETE no action ON UPDATE no action NOT DEFERRABLE;
-- Create indices
CREATE INDEX contracts_contract_nubmer_idx
  ON detail.contracts (contract_number);
CREATE INDEX contracts_ts_idx
  ON detail.contracts (ts);
-- Grant object privileges
GRANT SELECT ON detail.contracts TO reports_role;
GRANT SELECT ON detail.contracts TO john_gabriel;

DDL Interface

In Database Tour, there are several places, where the database objects' DDL can be seen. Please make sure the Object Browser is visible.

Extracting DDL for existing object

To see DDL for an existing object, please select the object in Object Browser and switch to Script page.

Alternatively, you can right-click selected object and choose Generate SQL | DDL | Create DDL Script.... This way, you can choose what parts of the DDL to extract. The interface is similar to the dialog shown below.

Generating DDL for new object

To generate DDL for a new object, based on an existing one, select an existing object in Object Browser and click Duplicate button above the object list. In the Duplicate Object dialog, choose Generate SQL option and check other options below, then click OK. See more details here.

Generating DDL for multiple objects

To get DDL for multiple objects, select the needed objects in Object Browser, right-click selected objects and choose Generate SQL | DDL | Create DDL Script... or Generate SQL | DDL | Save DDL to File(s).... In each case, a new dialog window will be shown, where you can choose DDL options and make some changes in the list of the objects. Here are differences between these two tasks:

Create DDL script. A new SQL window will be created with a new DDL script for all selected objects.
Save DDL to file(s). Here, you decide either to save code of all objects into one target file or spread the code to multiple files, one file per database object. In the latter case, you use a simple formula which builds the file path based on the object name.

Here, you can save DDL options and list of database objects to DDL settings file. The settings can be loaded then either by clicking Load button in this dialog or by choosing Tools | Generate DDL | Load Settings from File... menu.

Generating DDL Options

Add DROP command	Choose this option to add a SQL command to drop the object. This command is added before the main DDL code of each object in list.
CASCADE	Choose this option to extend the DROP command by adding CASCADE to it. DROP ... CASCADE command drops the database object itself and all objects which depend on it. Not all databases support cascade dropping.
Comment this command	Choose this option to comment the DROP command. Commented SQL command will not be executing when running the generated script. But when you realize you need to drop the existing objects before applying the DDL, you can easily restore these commands by uncommenting them in this application or in any text editor.
Add IF (NOT) EXISTS	Choose this option to add IF NOT EXISTS condition to all CREATE commands in the script and IF EXISTS condition to all DROP commands in the script (for databases that support this). Some databases (e.g. PostgreSQL) allow to create or drop database objects depending on their existence to avoid exceptions when the objects exist or do not exist: DROP <object_type> IF EXISTS <object_name> or CREATE <object_type> IF NOT EXISTS <object_name> Please note that not all database support this conditions for all object types.
Include partitions	For partitioned PostgreSQL tables only. Specify either to include the table partitions (if any) in the DDL code of partitioned table. In PostgreSQL databases (version 10 and above), table partitions are functioning like separate tables, so DDL code of the partitions can be included either inside the DDL code of the parent table (if this option is on) or can be included as DDL of separate tables (if table partitions are present in the list of objects for the script). Note: Be sure not to include partitioned tables with this option turned on and partitions as separate tables in the same time, because this will lead to duplicating the code of the partitions.
Include privileges	Specify either to include the object privileges in the DDL script. Not all databases support object privileges.
Include comments	Specify either to include the object comments in the DDL script. Not all databases support comments on database objects, or support them not for all object types.
Include indices	Only for tables. Specify either to include the table indices DDL in the script.
Include primary key	Only for tables. Specify either to include the primary key of the table in the table DDL.
Include foreign keys	Only for tables. Specify either to include the table foreign keys in the table DDL.
Place at the end of the script	Specify either to group all table foreign keys at the end of the script to avoid conflicts when some table in the script depends on another table which is created later in the script. When this option is off, the constraints are placed either inside the corresponding table's DDL, or follow immediately after the table's DDL.
Include constraints	Only for tables. Specify either to include the table constraints (except primary and foreign keys) in the table DDL.

The completeness and accuracy of the generated DDL code depends on used database engine and database type. This functionality most fully implemented for Oracle, PostgreSQL, Interbase, Firebird, and partially MySQL and SQL Server databases. For other types of databases, only table DDL has been implemented.

See also

Database Objects