DDL Commands

Data Definition Language (DDL) commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands...

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.

ALTER

Used to modify the structure of an existing table.

Add a Column

sql example

ALTER TABLE Employee ADD COLUMN middle_name VARCHAR(50);
ALTER TABLE Employee ADD COLUMN manager_id INT;

Drop a Column

sql example

ALTER TABLE Employee DROP COLUMN middle_name;

Modify a Column

sql example

-- 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

sql example

-- 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

sql example

ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee DROP FOREIGN KEY fk_manager;

Rename a Table

sql example

ALTER TABLE Employee RENAME TO Staff;
-- or
RENAME TABLE Staff TO Employee;

DROP

Used to permanently delete a database object and all its data.

sql example

-- 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.

sql example

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

sql example

-- 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

sql example

-- 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, and DEFAULT constraints where rules are known.
Create indexes only on columns that are searched, joined, or sorted frequently.
Avoid running DROP or TRUNCATE on live data without backup.

Overview

Related topics: DML Commands, Constraints in SQL, Indexes in DBMS, and Database Schema.

Why DDL is Important

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.

ALTER

Used to modify the structure of an existing table.

Add a Column

sql example

ALTER TABLE Employee ADD COLUMN middle_name VARCHAR(50);
ALTER TABLE Employee ADD COLUMN manager_id INT;

Drop a Column

sql example

ALTER TABLE Employee DROP COLUMN middle_name;

Modify a Column

sql example

-- 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

sql example

-- 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

sql example

ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee DROP FOREIGN KEY fk_manager;

Rename a Table

sql example

ALTER TABLE Employee RENAME TO Staff;
-- or
RENAME TABLE Staff TO Employee;

DROP

Used to permanently delete a database object and all its data.

sql example

-- 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.

sql example

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

sql example

-- 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

sql example

-- 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, and DEFAULT constraints where rules are known.
Create indexes only on columns that are searched, joined, or sorted frequently.
Avoid running DROP or TRUNCATE on live data without backup.

DDL Commands

Overview

Why DDL is Important

ALTER

Add a Column

Drop a Column

Modify a Column

Add a Constraint

Drop a Constraint

Rename a Table

DROP

TRUNCATE

RENAME

DDL and the Data Dictionary

Viewing Schema Information

DDL Best Practices

Continue learning this concept

DDL Commands

Overview

Why DDL is Important

ALTER

Add a Column

Drop a Column

Modify a Column

Add a Constraint

Drop a Constraint

Rename a Table

DROP

TRUNCATE

RENAME

DDL and the Data Dictionary

Viewing Schema Information

DDL Best Practices

Continue learning this concept