When you buy through affiliate links in our content, we may earn a commission at no extra cost to you. Learn What Does Data Definition Language Mean?
A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc.
This term is also known as data description language in some contexts, as it describes the fields and records in a database table.
Techopedia Explains Data Definition Language
The present database industry incorporates DDL into any formal language describing data. However, it is considered to be a subset of SQL (Structured Query Language). SQL often uses imperative verbs with normal English such as sentences to implement database modifications. Hence, DDL does not show up as a different language in an SQL database, but does define changes in the database schema.
It is used to establish and modify the structure of objects in a database by dealing with descriptions of the database schema. Unlike data manipulation language (DML) commands that are used for data modification purposes, DDL commands are used for altering the database structure such as creating new tables or objects along with all their attributes (data type, table name, etc.).
Commonly used DDL in SQL querying are CREATE, ALTER, DROP, and TRUNCATE.
Create
This command builds a new table and has a predefined syntax. The CREATE statement syntax is:
CREATE TABLE [table name] ([column definitions]) [table parameters];
For example:
CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);
The mandatory semi-colon at the end of the statement is used to process every command before it. In this example, the string CHAR is used to specify the data type. Other data types can be DATE, NUMBER, or INTEGER.
Alter
An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table.
An alter command syntax is:
ALTER object type object name parameters;
For example:
ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);
In this example, we added a unique primary key to the table to add a constraint and enforce a unique value. The constraint “employee_pk” is a primary key and is on the Employee table.
Drop
A drop command is used to delete objects such as a table, index or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it.
Drop statement syntax is:
DROP object type object name;
For example:
DROP TABLE Employee;
In this example, we’re deleting the Employee table.
Truncate
Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later.
Truncate statement syntax is:
TRUNCATE TABLE table_name;
For example:
TRUNCATE TABLE Employee;
In this example, we’re marking all the extents of the Employee table for deallocation, so they’re considered empty for reuse.
Other statements
Other commonly used commands include RENAME and COMMENT. The first one is used with the ALTER TABLE statement to change the name of an object (table, column, etc.). COMMENT is used to add single line, multi-line and in-line comments.
