Data Definition Language (DDL) is a set of statements for defining and changing the structures managed by a database management system (DBMS). In SQL, DDL includes commands such as CREATE, ALTER, and DROP, which establish, modify, or remove database objects. These objects include tables, views, schemas, and constraints, as well as implementation-specific structures such as indexes. DDL primarily describes how data is organized and governed, rather than requesting changes to individual records.
Scope and relationship to other database languages
DDL defines a database schema: the organization of database objects, their attributes, and the rules that apply to them. In a relational database, this includes table and column names, data types, relationships, and integrity constraints.
DDL is commonly distinguished from other categories of SQL statements:
| Category | Main purpose | Typical statements |
|---|---|---|
| Data Definition Language | Define or change database structures | CREATE, ALTER, DROP |
| [[data-manipulation-language | Data Manipulation Language]] (DML) | Retrieve or modify stored data |
| Data Control Language (DCL) | Manage authorization | GRANT, REVOKE |
| Transaction control | Establish or complete transactional work | START TRANSACTION, COMMIT, ROLLBACK |
These categories are useful teaching and documentation conventions, but their boundaries are not universal. SELECT is sometimes placed in a separate “Data Query Language” category. Some product documentation groups privilege-management statements with DDL. TRUNCATE, which removes all rows from a table while retaining its definition, is often classified as DDL despite its direct effect on stored data.
DDL is not necessarily a separate programming language. In SQL systems, it is a subset of the language used through the same interfaces as queries and updates. Other database systems expose comparable structural operations through specialized languages or an application programming interface.
Principal operations
Creating objects
CREATE statements introduce objects into a database. Depending on the system, these can include:
- Tables and schemas.
- Views and materialized views.
- Indexes.
- Sequences and user-defined types.
- Stored routines and triggers.
- Other objects, such as storage areas or external-table definitions.
A table definition ordinarily specifies each column’s name and data type. It may also specify default values, generated values, and constraints.
A view defines a named query rather than an ordinary independently stored table. A materialized view stores a query result and has additional maintenance or refresh requirements. Support for these objects, and the syntax used to define them, varies by DBMS.
Altering objects
ALTER statements change existing definitions. Typical operations include adding or removing columns, changing column types, and adding constraints.
The physical consequences depend on the requested change and the implementation. Adding a column may require only a catalog update, or it may require rewriting existing records. Changing a data type can require conversion of stored values and may fail if some values cannot be converted.
Renaming objects is another structural operation. Some systems express it through ALTER; others provide a separate RENAME statement.
Removing objects
DROP statements remove object definitions. Dropping a table ordinarily removes both its definition and its stored rows. Dropping a view removes the view definition without deleting the underlying tables.
Dependencies affect whether a drop is allowed. A system may reject an operation if other objects depend on the target, or it may support a CASCADE option that removes dependent objects as well. The objects affected by cascading behavior vary by implementation.
Constraints and integrity
DDL can express rules that the DBMS enforces on stored data. Common constraints include:
NOT NULL, which prevents a column from containing null values.UNIQUE, which requires uniqueness across one or more columns, subject to the system’s treatment of nulls.- Primary keys, which identify rows and combine uniqueness with a prohibition on null key values.
- Foreign keys, which require references to correspond to an eligible key in another table or the same table.
CHECKconstraints, which require a condition to hold according to SQL’s constraint-evaluation rules.
These constraints contribute to data quality by placing rules inside the database rather than relying solely on application code.
Constraints and indexes are related but distinct. A constraint expresses a logical rule; an index is an access structure. A DBMS may create or reuse an index to enforce a primary-key or unique constraint, but not every index represents a constraint, and not every constraint is enforced through an index.
Defaults also differ from constraints. A default supplies a value when an insertion omits one; it does not ordinarily prevent an application from supplying a different value.
Example
The following SQL illustrates a small schema and a subsequent structural change:
CREATE TABLE department (
department_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
employee_id INTEGER PRIMARY KEY,
department_id INTEGER,
name VARCHAR(100) NOT NULL,
salary DECIMAL(12, 2),
CONSTRAINT employee_salary_nonnegative
CHECK (salary >= 0),
CONSTRAINT employee_department_fk
FOREIGN KEY (department_id)
REFERENCES department (department_id)
);
ALTER TABLE employee
ADD COLUMN email VARCHAR(254);
CREATE INDEX employee_department_idx
ON employee (department_id);
The primary keys identify departments and employees. The foreign key requires each non-null employee department reference to identify an existing department. The index provides an access structure that may help queries using department_id; it is separate from the foreign-key declaration.
Because salary is nullable, its CHECK constraint does not by itself require a salary value. In SQL, a check constraint generally rejects a row when its condition is false, not when the condition evaluates to unknown because of a null. Requiring a salary would also require NOT NULL.
The example uses broadly familiar syntax, but exact support and behavior remain product-dependent.
Execution and metadata
DDL updates the DBMS’s internal descriptions of database objects, often stored in system catalogs or a data dictionary. These descriptions are metadata: information about the structure and properties of stored data.
Executing a DDL statement can involve several steps:
- Parsing the statement and checking its syntax.
- Verifying names, permissions, and dependencies.
- Recording new or changed object definitions.
- Creating or transforming physical storage where necessary.
- Updating dependency information and invalidating affected cached query plans.
DDL is generally declarative: it specifies the intended structure while the DBMS determines how to implement the change. A short declaration can therefore trigger substantial work, including scanning a table, validating constraints, building an index, or rewriting data.
Transactions and concurrency
The interaction between DDL and a database transaction is implementation-dependent.
In PostgreSQL, many schema changes can occur within an explicit transaction and be rolled back, although some operations have restrictions. In MySQL, many DDL statements cause implicit commits and are not undone by an ordinary user-issued rollback. Oracle Database also uses implicit commits around many DDL operations.
Atomic DDL does not necessarily mean user-rollbackable DDL. An implementation may guarantee that an individual schema operation completes consistently or is recovered after a failure without allowing users to group it with other work and later undo it. This distinction separates statement-level atomicity and crash recovery from broader transactional behavior associated with ACID properties.
DDL can also affect concurrency. Schema changes may acquire locks that delay queries or updates, wait for existing transactions, or contribute to deadlocks. Features described as “online” DDL often reduce disruption but do not necessarily eliminate all locking or resource costs.
Schema evolution and deployment
DDL is a principal mechanism for database schema migration: changing a database structure as an application evolves. Migration tools commonly record ordered changes and track which have been applied.
Migration scripts can be maintained in version control, reviewed alongside application changes, and tested in deployment pipelines. A definition file describes a desired structure, whereas a migration describes a transition from one structure to another. These are related but different artifacts.
Changes that preserve existing application behavior, such as introducing an optional column, are generally easier to deploy than changes that immediately remove or reinterpret existing structures. More complex migrations may separate structural changes, data conversion, application updates, and removal of obsolete objects.
A reverse migration is not necessarily a recovery mechanism. Recreating a dropped column does not restore its former contents. Recovery from destructive changes may require a backup or another data-recovery facility.
Portability and security
SQL standardization provides a shared foundation, but DDL remains strongly influenced by individual database implementations. Differences include supported data types, generated-key mechanisms, identifier rules, index options, storage settings, constraint behavior, and transactional semantics. Even similarly named commands can have different locking and performance characteristics.
DDL also carries significant security implications. Structural privileges can permit deletion of data, modification of integrity rules, or changes that disrupt applications. Systems therefore distinguish ordinary data-access permissions from permissions to create, alter, or drop objects. Access control and the principle of least privilege are relevant because an application account that only needs to read and update records does not necessarily need authority to redefine their structure.