SQL ALTER TABLE

SQL ALTER TABLE statement modify the structure of an existing table in a database. Whether adding new columns, modifying existing ones, deleting columns or renaming them. ALTER TABLE statement enables you to make changes without losing data stored in the table.

Syntax:

ALTER TABLE table_name [ADD | DROP | MODIFY] column_name datatype;

Parameters:

• table_name: name of the table you want to modify.
• ADD: used to add a new column.
• DROP: used to remove an existing column.
• MODIFY: used to change datatype or definition of an existing column.

Common Use Cases for SQL ALTER TABLE

1. ADD

The ADD clause is used to add a new column to an existing table. You must specify the name of the new column and its data type.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Students
ADD Email varchar(255);

Here, we are adding a column named Email to Student table

2. MODIFY

The MODIFY (or ALTER COLUMN in some databases like SQL Server) clause is used to modify the definition of an existing column, such as changing its data type or size.

Syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

ALTER TABLE Students
MODIFY COLUMN Address VARCHAR(100);

Here, we are modifying the column named Address datatype that is VARCHAR(100).

3. DROP

The DROP clause allows you to remove a column from a table. Be cautious when using this command as it will permanently remove the column and its data.

Query:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Students
DROP COLUMN Grade;

Here, we are removing a column named Grade from Student table

4. RENAME COLUMN

We can rename an existing column using RENAME COLUMN clause. This allows you to change the name of a column while preserving its data type and content.

Query:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Example:

ALTER TABLE Customer
RENAME COLUMN CustomerName TO FirstName;

This renames the column CustomerName to FirstName in the Customer table.

5. RENAME TO

We can rename an entire table using the RENAME TO clause. This changes the name of the table while preserving its structure and data.

Query:

ALTER TABLE table_name
RENAME TO new_table_name;

Example:

ALTER TABLE Customer
RENAME TO Clients;

This renames the table from Customer to Clients.

Example Queries

Let's explore ALTER TABLE queries using a Student table with columns ROLL_NO and NAME and demonstrate various modifications such as adding, modifying and dropping columns.

Here's how you can execute these operations:

1. Adding Columns (AGE and COURSE)

To add new columns AGE and COURSE to the Student table, use ALTER TABLE statement with the ADD clause.

Query:

ALTER TABLE Student ADD
(AGE number (3), COURSE VARCHAR (40));

Explanation: This adds an AGE column (numeric) and a COURSE column (VARCHAR(40)). Initially, these columns will be empty for existing rows.

Output

2. Modify Column COURSE to Reduce its Size

To reduce the size of the COURSE column from VARCHAR(40) to VARCHAR(20), use the MODIFY clause.

Query:

ALTER TABLE Student
MODIFY COURSE varchar(20);

Explanation: COURSE column will now allow a maximum of 20 characters instead of 40.

3. Drop COURSE Column from Student Table

To remove the COURSE column from the Student table, use the DROP COLUMN clause.

Query:

ALTER TABLE Student
DROP COLUMN COURSE;

Explanation: This permanently deletes the COURSE column from the table.

Output