Database Objects' 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:

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

To generate DDL for a new object, based on an existing one, select the 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.

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 change the order 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.

See also

Database Objects