Explore What is SQL ALTER TABLE Command with Examples

Did you know that SQL is the most popular database management language? It is popular due to its ease of use and learning as well. According to data, there are around 7 million SQL developers worldwide. One such feature that makes SQL popular is the ALTER TABLE command. The ALTER command in SQL is a data definition language (DDL) command which helps to change the structure of a table. With the use of the ALTER TABLE command, it is possible to modify a table’s schema without completely rebuilding it.

Let’s dive deeper into and understand more about the alter command in SQL.

SQL ALTER TABLE Command

The ALTER statement in SQL is used to modify the structure of a database table. It enables you to add, delete or modify columns in a table, as well as add constraints and indexing strategies. This command can also be used to rename tables, drop existing tables or even combine two tables into one.

When changing the structure of your tables, the ALTER TABLE command can be a useful tool. However, it’s essential to use it with caution because it could mess up your data. Any ALTER TABLE commands should always be tested on a test database before being executed on your production database. To learn more about ALTER query in SQL, you can pursue a SQL course.

SQL ALTER TABLE Statement

The SQL ALTER TABLE statement is used to change an already existing table. 

-- add order id column to Customers table
ALTER TABLE Customers
ADD phone varchar(10);

The SQL statement adds a column order id to the customer’s table.

ALTER TABLE Syntax

Let us understand the ALTER syntax in SQL: 

ALTER TABLE table_name
clause supporting_codes;

In the syntax above,

• Table_name is the table to be modified.
• The clause gives us information about how the table will be altered.
• Supportng_codes support the clause.

ALTER TABLE Operations

Several operations can be performed using the ALTER TABLE command. Let’s have a look at some of the operations.

1. Add a Column

We can add columns in a table with the ADD clause.

For example:

-- add order ID column to Customers table
ALTER TABLE Customers
ADD phone varchar(10);

In the above example, the order ID column gets added to the customer table.

2. Add Multiple Columns

We can also add multiple columns to a table. 

For example:

-- add phone and order ID columns to the Customers table
ALTER TABLE Customers
ADD phone varchar(10), age int;

Here, the phone and order ID columns get added to the table.

3. Rename Column in a Table

We can rename a column in a table using the RENAME clause.

For example:

-- rename column customer_id to q_id
ALTER TABLE Customers
RENAME COLUMN customer_id TO q_id;

Here the SQL command changed the column name of customer_id to q_id.

4. Modify a Column

We can also change the data type of a column in SQL using the MODIFY or ALTER COLUMN clause.

Let us look at the following examples:

• SQL Server

ALTER TABLE Customers
ALTER COLUMN phone number VARCHAR(2);

• MySQL

ALTER TABLE Customers
MODIFY COLUMN phone number VARCHAR(2);

• Oracle

ALTER TABLE Customers
MODIFY phone number VARCHAR(2);

• PostgreSQL

ALTER TABLE Customers
ALTER COLUMN phone number TYPE VARCHAR(2);

Here the SQL query changes the datatype of the phone number column to varchar in the customer table.

Alter table command can also be utilized to do the following operations.

5. Drop a Column

To delete/remove items in a column in a table the DROP clause is used. Let’s have a look at an example.

-- delete city column from Customers table
ALTER TABLE Customers
DROP COLUMN city;

Here, the SQL query removes the city column from the customers’ table.

6. Alter Column Datatype in SQL

ALTER TABLE employees
ALTER COLUMN birth_date DATE;

This statement will change the data type of the birth_date column in the employees’ table from VARCHAR(255) to DATE.

7. Rename a Table

With the help of the RENAME clause, we can change the name of a table.

-- rename Customers table to Old_customers
ALTER TABLE Customers
RENAME TO Old_customers;

Here, the SQL query renames the customers’ table to old_customers.

Conclusion

In conclusion, the use of the ALTER command in SQL provides a powerful and flexible tool for making changes to existing databases. It is an important tool to have in any administrator’s arsenal when managing databases and should be used carefully and with caution as any mistakes could cause data loss or other issues.

FAQs