Schema Modification

In real world database systems, data and requirements change over time.

As applications grow, developers often need to:

SQL provides specific commands to modify table structure and manage table data efficiently.

In this chapter, we will learn four important commands:

Each command serves a different purpose and must be used carefully to avoid data loss.

When Do We Need These Commands?

Understanding the difference between these commands is essential for safe and effective database management.

1. SQL ALTER Command

The ALTER command is used to change the structure of an existing table without deleting the table itself.

It allows you to

ALTER affects the table structure, not the data directly.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example: Adding a New Column

ALTER TABLE students
ADD age INT;

This adds a new column named age to the students table.

2. SQL CHANGE Command (MySQL)

The CHANGE command is mainly used in MySQL to rename a column and optionally change its datatype.

The column's new datatype must always be specified.

Syntax

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype;

Example

ALTER TABLE students
CHANGE marks score INT;

3. SQL MODIFY Command (MySQL)

The MODIFY command is used to change the datatype or size of a column without changing its name.

This is useful when existing column limits are no longer sufficient.

Syntax

ALTER TABLE table_name
MODIFY column_name new_datatype;

Example

ALTER TABLE students
MODIFY name VARCHAR(100);

4. SQL TRUNCATE Command

The TRUNCATE command is used to remove all records from a table at once.

It is commonly used when a table needs to be reset or cleared completely.

TRUNCATE removes all data but keeps the table structure intact.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE students;

Two Minute Drill