What is DDL?

Data Definition Language (DDL) is a category of SQL commands used to define, modify, and remove the structure of database objects such as databases, tables, indexes, views, and schemas. DDL commands deal with the "skeleton" of the database — not the data itself.

Every time you create a new database, design a table, add a column, or drop an index, you're using DDL commands. These commands are auto-committed, meaning their changes are permanent and cannot be rolled back like DML operations.

Main DDL Commands in MySQL

MySQL provides five core DDL commands to manage the structure of your database:

1. CREATE Command

The CREATE command is used to create new database objects such as databases, tables, indexes, views, and procedures.

🔹 CREATE DATABASE

-- Create a new database
CREATE DATABASE SchoolDB;

-- Create only if it doesn't already exist
CREATE DATABASE IF NOT EXISTS SchoolDB;

-- Switch to use the new database
USE SchoolDB;

-- Show all available databases
SHOW DATABASES;

🔹 CREATE TABLE

CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    city VARCHAR(50) DEFAULT 'Kolkata',
    email VARCHAR(100) UNIQUE,
    admission_date DATE DEFAULT (CURRENT_DATE)
);

🔹 CREATE TABLE from Another Table

-- Copy structure + data from another table
CREATE TABLE Students_Backup AS
SELECT * FROM Students;

-- Copy only structure (no data)
CREATE TABLE Students_Empty AS
SELECT * FROM Students WHERE 1 = 0;

🔹 CREATE INDEX

-- Create an index to speed up queries
CREATE INDEX idx_name ON Students(name);

-- Create a unique index
CREATE UNIQUE INDEX idx_email ON Students(email);

🔹 CREATE VIEW

-- Create a virtual table (view) from a query
CREATE VIEW ActiveStudents AS
SELECT student_id, name, city
FROM Students
WHERE age >= 18;

-- Use it like a normal table
SELECT * FROM ActiveStudents;

2. ALTER Command

The ALTER command is used to modify the structure of an existing table — add, change, or remove columns and constraints.

🔹 ADD New Column

ALTER TABLE Students 
ADD phone VARCHAR(15);

-- Add multiple columns at once
ALTER TABLE Students 
ADD gender VARCHAR(10),
ADD blood_group VARCHAR(5);

🔹 MODIFY Column Data Type

ALTER TABLE Students 
MODIFY age TINYINT;

-- Change column with NOT NULL constraint
ALTER TABLE Students 
MODIFY name VARCHAR(150) NOT NULL;

🔹 CHANGE Column Name

ALTER TABLE Students 
CHANGE city location VARCHAR(100);

🔹 DROP Column

ALTER TABLE Students 
DROP COLUMN phone;

🔹 ADD / DROP Constraints

-- Add a UNIQUE constraint
ALTER TABLE Students 
ADD CONSTRAINT uq_email UNIQUE (email);

-- Add a FOREIGN KEY
ALTER TABLE Enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES Students(student_id);

-- Drop a constraint
ALTER TABLE Students 
DROP INDEX uq_email;

🔹 RENAME Table

ALTER TABLE Students 
RENAME TO StudentsRecord;

3. DROP Command

The DROP command is used to permanently delete a database object such as a table, database, view, or index. Warning: This action cannot be undone!

🔹 DROP TABLE

-- Permanently delete a table and its data
DROP TABLE Students;

-- Drop only if it exists (avoids error)
DROP TABLE IF EXISTS Students;

🔹 DROP DATABASE

-- Permanently delete an entire database
DROP DATABASE SchoolDB;

-- Drop only if exists
DROP DATABASE IF EXISTS SchoolDB;

🔹 DROP VIEW / INDEX

DROP VIEW ActiveStudents;
DROP INDEX idx_name ON Students;

4. TRUNCATE Command

The TRUNCATE command is used to delete all rows from a table while keeping its structure intact. It's much faster than DELETE because it doesn't log each row deletion.

-- Removes all data but keeps the table structure
TRUNCATE TABLE Students;

DELETE vs TRUNCATE vs DROP — Quick Comparison

5. RENAME Command

The RENAME command is used to rename a table or sometimes a column (via ALTER).

-- Rename a single table
RENAME TABLE Students TO StudentRecords;

-- Rename multiple tables at once
RENAME TABLE 
Teachers TO Faculty,
Courses TO Subjects;

Common Constraints Used in DDL

Constraints are rules applied to columns to maintain data integrity. They are defined at table creation or via ALTER.

Example: Combining All Constraints

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    department VARCHAR(50) DEFAULT 'General',
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)
);

Key Characteristics of DDL

Real-World Applications of DDL

Best Practices for Using DDL

Common Mistakes to Avoid