Database definition

What is Data Definition Language (DDL)?

What does Data Definition Language (DDL) 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 because it describes fields and records in a database table.

Techopedia Explains Data Definition Language (DDL)

Today’s database industry integrates DDL into any formal language describing data. However, it is considered a subset of SQL (Structured Query Language). SQL often uses imperative verbs with normal English, such as sentences, to implement database changes. Therefore, DDL does not appear as a different language in an SQL database, but defines 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 which are used for data modification purposes, DDL commands are used to modify the database structure like creating new tables or objects with all their attributes (data type, table name, etc.).

DDLs commonly used in SQL queries are CREATE, ALTER, DROP, and TRUNCATE.

Create

This command creates a new table and has a predefined syntax. The syntax of the CREATE statement is:

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

For instance:

CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);

The required semicolon at the end of the statement is used to process all commands that precede it. In this example, the CHAR string is used to specify the data type. Other data types can be DATE, NUMBER, or INTEGER.

Edit

An alter command modifies an existing database table. This command can add an additional column, delete 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 instance:

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 “employee_pk” constraint is a primary key and is on the Employee table.

Drop

A delete command is used to delete objects such as a table, index, or view. A DROP statement cannot be undone, so once an object is destroyed there is no way to recover it.

The syntax of the Drop statement is:

DROP object type object name;

For instance:

DELETE TABLE Employee;

In this example, we drop the Employee table.

Truncate

Similar to DROP, the TRUNCATE statement is used to quickly delete all records from a table. However, unlike DROP which completely destroys a table, TRUNCATE preserves its complete structure for later reuse.

The syntax of the truncated statement is:

TRUNCATE TABLE table_name;

For instance:

TRUNCATE TABLE Employee;

In this example, we are marking all extents of the Employee table for deallocation, so they are considered empty for reuse.

Other statements

Other commonly used commands include RENAME and COMMENT. The first 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 inline comments.