Data Definition Language (DDL) in SQL: CREATE, ALTER, DROP Explained

Quick answer: Learn SQL Data Definition Language commands including CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE, with syntax and examples.

What is Data Definition Language?

DDL is the subset of SQL commands used to define and modify the structure of database objects, such as tables, rather than the data stored inside them. The main DDL commands are CREATE, ALTER, DROP and TRUNCATE.

CREATE TABLE

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

This defines a new table with its columns, data types and constraints. PRIMARY KEY uniquely identifies each row, NOT NULL requires a value, and UNIQUE prevents duplicate values in that column.

ALTER TABLE

ALTER TABLE modifies an existing table's structure without deleting its data.

-- Add a new column
ALTER TABLE students ADD COLUMN phone VARCHAR(15);

-- Modify a column's data type
ALTER TABLE students MODIFY COLUMN name VARCHAR(150);

-- Rename a column
ALTER TABLE students RENAME COLUMN phone TO mobile_number;

-- Drop a column
ALTER TABLE students DROP COLUMN mobile_number;

Exact syntax for modifying and renaming columns varies slightly between database systems such as MySQL, PostgreSQL and SQL Server.

DROP TABLE

DROP TABLE students;

Permanently deletes the entire table, including its structure and all its data. This cannot be undone, so it should be used with real caution in a production database.

TRUNCATE TABLE

TRUNCATE TABLE students;

Removes all rows from a table while keeping the table structure itself intact, ready to be filled again.

DROP vs TRUNCATE vs DELETE

DELETE is a Data Manipulation Language command, not DDL, since it works on data rather than structure, but it is worth understanding alongside TRUNCATE for this exact comparison.

Common Constraints Used in DDL

• PRIMARY KEY: uniquely identifies each row

• FOREIGN KEY: links a column to a primary key in another table

• NOT NULL: requires a value in that column

• UNIQUE: prevents duplicate values

• CHECK: enforces a specific condition on the column's values

Common Interview Questions

What is the difference between DROP and TRUNCATE?

DROP removes the entire table, including its structure. TRUNCATE removes all the rows but keeps the table structure intact for future use.

Why is TRUNCATE generally faster than DELETE for clearing a whole table?

TRUNCATE deallocates the data pages directly without logging each individual row deletion, while DELETE logs each row removed, making TRUNCATE considerably faster for clearing an entire table.