ALTER command to make changes in SQL table

SQL

ALTER TABLE statement in SQL allows us to modify the structure of an existing table. It can be used to add, modify, or drop columns, as well as perform other alterations to the table’s characteristics. This command is essential for database administrators and developers to adapt the database schema according to changing requirements.

Syntax

The basic syntax of the ALTER TABLE command is as follows:

ALTER TABLE table_name

This syntax sets the stage for various modifications that can be applied to the specified table.

Let’s take an example Database:

StudentID	FirstName	LastName	Age	Grade
1	John	Doe	20	A
2	Jane	Smith	22	B
3	Alice	Johnson	21	C

Adding Columns

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

-- After ALTER TABLE
ALTER TABLE Students
ADD Email VARCHAR(50);

-- After ALTER TABLE
SELECT * FROM Students;

StudentID	FirstName	LastName	Age	Grade
1	John	Doe	20	A
2	Jane	Smith	22	B
3	Alice	Johnson	21	C

In this example, we’re adding a new column named Email to the Students table with a data type of VARCHAR and a maximum length of 50 characters.

Modifying Columns

Syntax:

ALTER TABLE table_name
MODIFY column_name new_datatype;

Example:

-- After ALTER TABLE
ALTER TABLE Students
MODIFY Grade CHAR(1);

-- After ALTER TABLE
SELECT * FROM Students;

StudentID	FirstName	LastName	Age	Grade
1	John	Doe	20	A
2	Jane	Smith	22	B
3	Alice	Johnson	21	C

Here, we change the data type of the Grade column in the Students table to CHAR(1).

Dropping Columns

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

-- After ALTER TABLE
ALTER TABLE Students
DROP COLUMN Age;

-- After ALTER TABLE
SELECT * FROM Students;

StudentID	FirstName	LastName	Grade
1	John	Doe	A
2	Jane	Smith	B
3	Alice	Johnson	C

This example removes the Age column from the Students table.

Renaming Columns

Syntax:

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Example:

-- After ALTER TABLE
ALTER TABLE Students
RENAME COLUMN Email TO ContactEmail;

-- After ALTER TABLE
SELECT * FROM Students;

StudentID	FirstName	LastName	Grade
1	John	Doe	A
2	Jane	Smith	B
3	Alice	Johnson	C

In this example, we’ve renamed the Email column to ContactEmail