WhatIs.com

Data Definition Language (DDL)

By Rahul Awati

What is Data Definition Language (DDL)?

Data Definition Language (DDL) is used to create and modify the structure of objects in a database using predefined commands and a specific syntax. These database objects include tables, sequences, locations, aliases, schemas and indexes.

Data Definition Language explained

DDL is a standardized language with commands to define the storage groups (stogroups), different structures and objects in a database. DDL statements create, modify and remove database objects, such as tables, indexes and stogroups. DDL is also used in a generic sense to refer to any language that describes data.

DDL includes Structured Query Language (SQL) statements to create and drop databases, aliases, locations, indexes, tables and sequences. It also includes statements to alter these objects and impose or drop certain constraints on tables, such as the following:

These constraints are used to enforce uniqueness, referential or domain integrity.

When a DDL statement is executed, it takes effect immediately in the database.

DDL is sometimes known as Data Description Language since its statements can also be used to describe, comment on and place labels on database objects.

DDL vs. SQL vs. DML vs. DQL

Since DDL includes SQL statements to define changes in the database schema, it is considered a subset of SQL. SQL uses normal English verbs to modify database objects, and DDL does not appear as a different language in a SQL database.

In Data Manipulation Language (DML), commands are used to modify data in a database. DML statements control access to the database data. In contrast, DDL commands are used to create, delete or alter the structure of objects in a database but not its data. DDL deals with descriptions of the database schema and is useful for creating new tables, indexes, sequences, stogroups, etc. and to define the attributes of these objects, such as data type, field length and alternate table names (aliases).

Data Query Language (DQL) is used to get data within the schema objects of a database and also to query it and impose order upon it. Like DDL, DQL is also a subset of SQL. One of the most common commands in DQL is SELECT. It lets users get data from a database table and perform some operation on it. When the statement is executed, the result is compiled into a temporary table and displayed by the front-end program or application.

Common Data Definition Language commands

General application users -- i.e., users who are not authorized to work directly with a database -- do not use DDL commands. These general users can and should only access the database indirectly via the application.

The most common command types in DDL are CREATE, ALTER and DROP. All three types have a predefined syntax that must be followed for the command to run and changes to take effect.

1. CREATE

Syntax

CREATE TABLE [table name] ([column definitions]) [table parameters];

The semicolon at the end of the command is used to process every command before it.

The CREATE group of DDL commands includes the following:

2. DROP

DDL also includes several DROP commands to delete objects in a database. DROP commands cannot be undone, so once an object is deleted, it cannot be recovered.

Syntax

DROP object type object name;

The most common DROP commands are the following:

3. ALTER

The third group of DDL commands is ALTER. These commands are used to make modifications to database objects, such as indexes, locations and stogroups.

Syntax

ALTER object type object name parameters;

The most common ALTER commands are the following:

4. Other commands

Apart from the CREATE, DROP and ALTER commands, DDL includes other commands:

Explore the difference between DDL and DML.

29 Jun 2022

All Rights Reserved, Copyright 1999 - 2024, TechTarget | Read our Privacy Statement