Data definition language (DDL)

What is data definition language?

Data definition language (DDL) is a subset of Structured Query Language (SQL) commands used to define or alter the structure of a database.

DDL commands can be used to create, alter, and drop objects such as tables, indexes, and schemas. DDL focuses on metadata and often requires elevated privileges to execute.

How does DDL work?

Infographic showing the DDL lifecycle, including DDL statements, changes, operational impact, security controls, and safety nets. DDL statements are used to execute schema-change statements on databases, updating system catalogs and metadata tables.

DDL commands can lock tables during structural changes, and in most databases, their effects are auto-committed by default. DDL changes are also recorded in logs for recovery and auditing purposes.

Types of DDL statements

These are the five main types of DDL statements:

CREATE: For creating a new database object.
ALTER: For modifying the structure of an existing object.
DROP: For removing objects permanently.
TRUNCATE: For emptying tables while maintaining their structure.
RENAME/COMMENT: For adjusting database names and descriptions or adding explanations to the code.

Where is it used?

DDL is used in:

Relational databases (MySQL, PostgreSQL, etc.).
Data warehouses and lakehouse SQL layers.
Migration tools and continuous integration (CI) and continuous delivery (CD) pipelines.
Infrastructure-as-Code (IaC) for databases.
Software-as-a-Service (SaaS) applications, managing tenant schemas.

Why is DDL important?

DDL enables consistent data models and is used to manage schema changes over time. It supports secure access control to individual database objects and segmentation. DDL also affects application availability and stability, and it’s often involved in compliance and audit processes.

Risks and privacy concerns

Elevated privileges may allow unintended schema changes.
Improperly constructed dynamic queries may introduce injection risks.
Structural changes can lock database objects and affect availability.
Removing objects may impact dependent systems and audit records.

FAQ

Is DDL the same as SQL?

No. Data definition language (DDL) is a subset of Structured Query Language (SQL) commands used to create, alter, or drop database objects.

What’s the difference between DDL and DML?

Data definition language (DDL) is used to define, create, and modify database objects such as tables and indexes. Data manipulation language (DML) is used to manipulate data within a database, such as inserting or updating individual records.

Do DDL statements auto-commit?

Yes, in most databases, data definition language (DDL) statements auto-commit, meaning that when you run a DDL command, the database automatically commits the transaction, and the change cannot be rolled back afterward.

However, some database systems support transactional DDL, allowing changes to be rolled back under certain conditions.

How does data definition language (DDL) relate to database security roles?

DDL operations are governed by database security roles and permissions, which determine who can create, modify, or delete database objects.