SQL ALTER TABLE

---

---

SQL ALTER TABLE Command

The ALTER TABLE command in SQL is used to change the structure of an existing table. It allows you to add new columns, delete existing columns, change column data types, and even rename columns or the table (in some databases).

Syntax

Following is the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name 
-- operation here (ADD / DROP / RENAME / MODIFY)

Different database systems (MySQL, SQL Server, PostgreSQL) have slight variations in syntax, which we will cover throughout this tutorial.

Let us explore the most common operations using the ALTER TABLE command, based on the CUSTOMERS table given below.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Let us insert some records in the table:

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

We get the following table which we will use in all examples:

ALTER TABLE: Add a Column

Sometimes you need to store new information in your database. To do this, you can add a new column to an existing table using the ALTER TABLE statement with the ADD keyword.

Syntax

Following is the basic syntax to add a column using ALTER TABLE statement in MySQL database:

ALTER TABLE table_name 
ADD column_name datatype; 

Example

In the following example, we add an EMAIL column to the CUSTOMERS table that can store up to 100 characters:

ALTER TABLE CUSTOMERS 
ADD EMAIL VARCHAR(100); 

We get the output as shown below:

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the CUSTOMERS table is altered by adding a new column EMAIL, use the SELECT statement to retrieve the records of the table:

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table will be displayed as follows:

ALTER TABLE: Drop a Column

If you no longer need a column, you can remove it from your table using the ALTER TABLE statement with the DROP COLUMN keyword.

Syntax

Here is the basic syntax to drop a column from a table in MySQL:

ALTER TABLE table_name 
DROP COLUMN column_name;

Example

In this example, we remove the AGE column from the CUSTOMERS table:

ALTER TABLE CUSTOMERS 
DROP COLUMN AGE;

The output obtained is as shown below:

Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

We can use the following SELECT statement to check if the AGE column is removed:

SELECT * FROM CUSTOMERS;

The CUSTOMERS table will now appear without the AGE column as shown in the table below:

ALTER TABLE: Modify Column Data Type

When you want to change the type of data a column holds, or adjust its size (like making a string longer or a number more precise), you can use the ALTER TABLE statement with the MODIFY COLUMN keyword in MySQL.

Syntax

Here is the basic syntax to modify a column in MySQL:

ALTER TABLE table_name 
MODIFY COLUMN column_name new_datatype;

Example

Now, we change the SALARY column in the CUSTOMERS table to have a smaller decimal format:

ALTER TABLE CUSTOMERS 
MODIFY COLUMN SALARY DECIMAL(10, 2);

Following is the output obtained:

Query OK, 7 rows affected (0.12 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To confirm the change, you can describe the structure of the table:

DESCRIBE CUSTOMERS;

The SALARY column will now show the updated data type and other column details as part of the table structure when you run the above command:

ALTER TABLE: Rename a Column

If you want to give a column a new name (for example, for clarity or to follow naming conventions), you can use the ALTER TABLE statement. The syntax depends on your database system.

MySQL Syntax

Following is the basic syntax to rename a column in MySQL database:

ALTER TABLE table_name 
CHANGE old_column_name new_column_name datatype;

MySQL Example

In the following example, we rename the NAME column to FULLNAME in the CUSTOMERS table in MySQL database:

ALTER TABLE CUSTOMERS 
CHANGE NAME FULLNAME VARCHAR(50);

The result produced is as shown below:

Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify the column has been renamed, use the DESCRIBE statement to view the table structure:

DESCRIBE CUSTOMERS;

You will now see the column listed as FULLNAME instead of NAME:

SQL Server Syntax

Following is the basic syntax to rename a column in SQL Server:

EXEC sp_rename 'table_name.old_column', 'new_column', 'COLUMN';

SQL Server Example

Here, we rename the NAME column to FULLNAME in the CUSTOMERS table in SQL Server:

EXEC sp_rename 'CUSTOMERS.NAME', 'FULLNAME', 'COLUMN';

We get the output as shown below:

Caution: Changing any part of an object name could break scripts and stored procedures.
Completion time: 2025-08-04T10:04:09.4015824+05:30

PostgreSQL Syntax

Following is the basic syntax to rename a column in PostgreSQL:

ALTER TABLE table_name 
RENAME COLUMN old_column TO new_column;

PostgreSQL Example

Now, we rename the NAME column to FULLNAME in the CUSTOMERS table in PostgreSQL:

ALTER TABLE STUDENTS 
RENAME COLUMN NAME TO FULLNAME;

After executing the above command, we get the following output:

ALTER TABLE

ALTER TABLE: Rename the Table

Sometimes you may want to change the name of a table entirely, for example, from CUSTOMERS to CLIENTS. You can do this using the ALTER TABLE statement. The syntax varies depending on the database system you are using.

MySQL Syntax

Following is the basic syntax to rename a table in MySQL:

RENAME TABLE old_table_name TO new_table_name;

MySQL Example

In the following example, we rename the CUSTOMERS table to CLIENTS in the MySQL database:

RENAME TABLE CUSTOMERS TO CLIENTS;

The result produced is as shown below:

Query OK, 0 rows affected (0.05 sec)

Verification

To verify whether the table name has been successfully changed, use the following command to list all tables:

SHOW TABLES;

The table CLIENTS will now be listed instead of CUSTOMERS:

SQL Server Syntax

Following is the basic syntax to rename a table in SQL Server:

EXEC sp_rename 'old_table_name', 'new_table_name';

SQL Server Example

Here, we rename the CUSTOMERS table to CLIENTS in SQL Server:

EXEC sp_rename 'CUSTOMERS', 'CLIENTS';

We get the output as shown below:

Caution: Changing any part of an object name could break scripts and stored procedures.
Completion time: 2025-08-04T10:11:24.0473044+05:30

PostgreSQL Syntax

Following is the basic syntax to rename a table in PostgreSQL:

ALTER TABLE old_table_name 
RENAME TO new_table_name;

PostgreSQL Example

Now, we rename the CUSTOMERS table to CLIENTS in the PostgreSQL database:

ALTER TABLE CUSTOMERS 
RENAME TO CLIENTS;

After executing the above command, we get the following output:

ALTER TABLE

Important Points to Know About ALTER TABLE

Following are the important points you should remember when altering tables in SQL databases:

• ALTER TABLE changes a table's structure without deleting its data.
• Renaming columns may affect views, procedures, or application code referencing them.
• Syntax varies slightly between MySQL, SQL Server, and PostgreSQL.
• Always back up your database before making structural changes.