DBMS Topics
DDL Commands
Last Updated : 21 May, 2026
Data Definition Language DDL commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands affect the structu
Overview
Data Definition Language (DDL) commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands affect the structure of the database, not the data itself.
DDL is used when a database is designed or when its structure needs to change. For example, creating a student table, adding a new column for email, defining a primary key, creating an index for faster search, or dropping an unused table are all DDL tasks. In most DBMSs, DDL commands are auto-committed, so they should be executed carefully on production databases.
Related topics: DML Commands, Constraints in SQL, Indexes in DBMS, and Database Schema.
Why DDL is Important
A database can store correct data only when its structure is well designed. DDL defines table names, column data types, keys, constraints, and relationships. A poor DDL design can create duplicate data, invalid records, slow queries, and difficult maintenance. A good DDL design supports normalization, data integrity, and efficient querying.
In exams, DDL questions usually ask you to create tables with primary key and foreign key constraints, alter a table, drop an object, or compare DELETE, TRUNCATE, and DROP.
CREATE
Used to create new database objects.
CREATE DATABASE
CREATE DATABASE university;
USE university;CREATE TABLE
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
location VARCHAR(100),
budget DECIMAL(15, 2) DEFAULT 0.00
);
CREATE TABLE Employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15),
salary DECIMAL(10, 2) CHECK (salary > 0),
hire_date DATE DEFAULT (CURRENT_DATE),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);CREATE INDEX
-- Simple index
CREATE INDEX idx_emp_salary ON Employee(salary);
-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON Employee(email);
-- Composite index
CREATE INDEX idx_emp_dept_salary ON Employee(dept_id, salary);CREATE VIEW
CREATE VIEW CS_Employees AS
SELECT emp_id, first_name, last_name, salary
FROM Employee
WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'Computer Science');ALTER
Used to modify the structure of an existing table.
Add a Column
ALTER TABLE Employee ADD COLUMN middle_name VARCHAR(50);
ALTER TABLE Employee ADD COLUMN manager_id INT;Drop a Column
ALTER TABLE Employee DROP COLUMN middle_name;Modify a Column
-- Change data type or size
ALTER TABLE Employee MODIFY COLUMN phone VARCHAR(20);
-- Rename a column (MySQL 8+)
ALTER TABLE Employee RENAME COLUMN phone TO mobile;Add a Constraint
-- Add a CHECK constraint
ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (salary BETWEEN 10000 AND 1000000);
-- Add a foreign key
ALTER TABLE Employee ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES Employee(emp_id);Drop a Constraint
ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee DROP FOREIGN KEY fk_manager;Rename a Table
ALTER TABLE Employee RENAME TO Staff;
-- or
RENAME TABLE Staff TO Employee;DROP
Used to permanently delete a database object and all its data.
-- Drop a table (structure + data)
DROP TABLE Employee;
-- Drop with safety check
DROP TABLE IF EXISTS Employee;
-- Drop an index
DROP INDEX idx_emp_salary ON Employee;
-- Drop a view
DROP VIEW IF EXISTS CS_Employees;
-- Drop a database
DROP DATABASE IF EXISTS university;Warning: DROP is irreversible. Unlike TRUNCATE, there is no way to recover the structure or data once dropped.
TRUNCATE
Removes all rows from a table but keeps the table structure intact.
TRUNCATE TABLE Employee;TRUNCATE vs. DELETE:
| Feature | TRUNCATE | DELETE |
|---|---|---|
| Removes | All rows | Specific or all rows |
| WHERE clause | Not supported | Supported |
| Rollback | Not possible (in most DBs) | Possible (within transaction) |
| Speed | Very fast (resets pages) | Slower (logs each row) |
| Triggers | Does not fire | Fires row-level triggers |
| Resets AUTO_INCREMENT | Yes | No |
RENAME
-- Rename a table
RENAME TABLE old_table_name TO new_table_name;
-- Rename a column
ALTER TABLE Employee RENAME COLUMN emp_id TO employee_id;DDL and the Data Dictionary
Every DDL command updates the system catalog (data dictionary). The DBMS automatically records:
- Table name, column names, data types
- Constraints (PK, FK, CHECK, UNIQUE)
- Index definitions
- User permissions
| INFORMATION_SCHEMA.TABLES | New row for the table |
| INFORMATION_SCHEMA.COLUMNS | Rows for each column |
| INFORMATION_SCHEMA.TABLE_CONSTRAINTS | PK and FK entries |
Viewing Schema Information
-- Show all tables in current database
SHOW TABLES;
-- Show table structure
DESCRIBE Employee;
-- or
SHOW COLUMNS FROM Employee;
-- Show create statement
SHOW CREATE TABLE Employee;
-- Query information schema
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'university';DDL Best Practices
- Choose meaningful table and column names.
- Use the smallest suitable data type for each column.
- Add primary keys to uniquely identify rows.
- Use foreign keys where tables are related.
- Add
NOT NULL,UNIQUE,CHECK, andDEFAULTconstraints where rules are known. - Create indexes only on columns that are searched, joined, or sorted frequently.
- Avoid running
DROPorTRUNCATEon live data without backup.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for DDL Commands.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, ddl, commands, ddl commands
Related DBMS Topics