Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

ALTER Command in SQL – A Quick Guide

Basics of SQL
Basics of SQL
icon
12 Hrs. duration
icon
12 Modules
icon
2600+ Learners
logo
Start Learning

When managing databases, it is a very general task to transform tables already created in a database. One can use the SQL ALTER command in this scenario to complete the job easily. With the use of this command, we can easily perform tasks like adding new columns, removing or modifying existing columns, and handling constraints and indexes.

 

It is equally important to understand the effective implementation of the ALTER command in SQL. This can save our time and maximise the flexibility of the database operations you want to achieve. It saves a lot of time, and modifications can be made on the structure level without re-creating tables or losing data. Let’s examine such a powerful SQL command in detail.

Adding New Columns to an Existing Table

New columns are often required in a table to accommodate more data. The ALTER TABLE statement makes this task quite easy.

 

Syntax for Adding a Single Column

 

ALTER TABLE table_name ADD column_name datatype;

 

Example

 

Imagine we have a table named Employees that currently looks like this:

 

ID Name Age
1 John 30
2 Alice 28
3 Bob 25

 

We want to add a new column, Email, to this table. We use the following SQL command:

 

ALTER TABLE Employees ADD Email VARCHAR(50);

 

Explanation

 

This command adds a new column, Email, with the data type VARCHAR(50). After executing the command, the Employees table will look like this:

 

ID Name Age Email
1 John 30 NULL
2 Alice 28 NULL
3 Bob 25 NULL

 

The new column Email has been added, and since no data was specified, it contains NULL values.

Adding Multiple Columns in a Single Statement

Sometimes, we need to add multiple columns at once. We can do this efficiently with the ALTER TABLE statement.

Syntax for Adding Multiple Columns

 

ALTER TABLE table_name ADD (column_name1 datatype, column_name2 datatype, …);

 

Example

 

Let’s extend our Employees table by adding two new columns, Phone and Address:

 

ALTER TABLE Employees ADD (Phone VARCHAR(15), Address VARCHAR(100));

 

Explanation

 

ALTER command in SQL adds the Phone and Address columns to the Employees table. After running the command, the table will look like this:

 

ID Name Age Email Phone Address
1 John 30 NULL NULL NULL
2 Alice 28 NULL NULL NULL
3 Bob 25 NULL NULL NULL

 

Now, the Employees table includes two additional columns, and both contain NULL values by default.

 

Modifying Existing Columns in SQL Tables

 

Over time, the requirements for data storage can change. We may need to modify existing columns to fit new data better. The ALTER TABLE statement helps us change column definitions.

 

Syntax for Modifying Columns

  • SQL Server:
ALTER TABLE table_name ALTER COLUMN column_name datatype;

 

  • MySQL / Oracle:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

 

  • PostgreSQL:

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

 

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

 

Example

 

Suppose the Phone column in our Employees table needs to be expanded to hold longer phone numbers:

 

ALTER TABLE Employees MODIFY COLUMN Phone VARCHAR(20);

 

Explanation

 

The ALTER command in SQL changes the Phone column’s data type to VARCHAR(20). This allows for storing longer phone numbers. The Employees table structure is updated without affecting existing data.

Dropping Columns from a Table

Sometimes, columns become redundant or are no longer needed. We can remove these columns using the ALTER TABLE statement.

 

Syntax for Dropping a Column

 

ALTER TABLE table_name DROP COLUMN column_name;

 

Example

 

Let’s remove the Address column from our Employees table:

 

ALTER TABLE Employees DROP COLUMN Address;

 

Explanation

 

ALTER command in SQL deletes the Address column from the Employees table. After executing the command, the table will look like this:

 

ID Name Age Email Phone
1 John 30 NULL NULL
2 Alice 28 NULL NULL
3 Bob 25 NULL NULL

 

The Address column is no longer part of the table structure.

Renaming Columns and Tables

Renaming the columns and tables is a usual process that one can stumble upon while working with databases. This means that your database remains well-organised and well-structured for easy comprehension.

Renaming Columns

There are cases where the name of a certain column should be altered to match the data stored in the column. This is how we can rename the columns using ALTER TABLE statement:

Syntax for Renaming a Column

 

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

 

Example

 

ID Name Age Email
1 John 30 john@example.com
2 Alice 28 alice@example.com
3 Bob 25 bob@example.com

 

We want to rename the Name column to FullName:

 

ALTER TABLE Employees RENAME COLUMN Name TO FullName;

 

Explanation

 

The Employees table will look like this after running this command:

 

ID FullName Age Email
1 John 30 john@example.com
2 Alice 28 alice@example.com
3 Bob 25 bob@example.com

 

The Name column is now FullName, making the table clearer.

Renaming Tables

Renaming a table can help maintain consistency in your database, particularly if the table’s intended use has changed.

 

Syntax for Renaming a Table

 

ALTER TABLE old_table_name RENAME TO new_table_name;

 

Example

 

If we want to rename the Employees table to Staff:

 

ALTER TABLE Employees RENAME TO Staff;

 

Explanation

 

After running the ALTER command in SQL, our table name will change from Employees to Staff, making it easier to understand if we now refer to all employees as staff.

DevOps & Cloud Engineering
Internship Assurance
DevOps & Cloud Engineering

Changing Data Types of Existing Columns

Sometimes, we must modify a column’s data type to accommodate new information. The ALTER TABLE command makes this simple.

 

Syntax for Changing Data Types

 

  • SQL Server:
ALTER TABLE table_name ALTER COLUMN column_name datatype;

 

  • MySQL / Oracle:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

 

  • PostgreSQL:
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

 

Example

 

Suppose we have an Employees table and need to change the Phone column to store longer phone numbers. The Employees table initially looks like this:

 

ID FullName Age Email Phone
1 John 30 john@example.com 123456789
2 Alice 28 alice@example.com 987654321
3 Bob 25 bob@example.com 555555555

 

We can modify the Phone column like this:

ALTER TABLE Employees MODIFY COLUMN Email VARCHAR(20);

 

Explanation

 

The ALTER command in SQL changes the Email column to allow up to 20 characters. After executing this, the structure of the Employees table will be updated, allowing us to store longer email IDs without affecting the existing data.

Adding and Dropping Constraints

Constraints are rules enforced on data columns to ensure data integrity. The ALTER TABLE statement helps us add and drop these constraints as needed.

Adding Primary Key Constraints

A primary key constraint ensures that each value in a column is unique and not null.

 

Syntax

 

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

 

Example

 

To add a primary key to the ID column in our Employees table:

 

ALTER TABLE Employees ADD CONSTRAINT pk_Employees_ID PRIMARY KEY (ID);

 

Explanation

 

This command adds a primary key constraint to the ID column, ensuring each ID is unique and not null.

Dropping Primary Key Constraints

If a primary key constraint is no longer needed, we can remove it.

 

Syntax

 

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

 

Example

 

To drop the primary key from the Employees table:

 

ALTER TABLE Employees DROP CONSTRAINT pk_Employees_ID;

 

Explanation

 

ALTER command in SQL removes the primary key constraint from the ID column.

Adding Foreign Key Constraints

Foreign key constraints link two tables together, ensuring that the values in a column correspond to values in another table.

 

Syntax

 

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table (column_name);

 

Example

 

Assume we have a Departments table, and we want to link Employees to Departments using a foreign key on the DepartmentID column:

 

DepartmentID DepartmentName
1 HR
2 IT
3 Finance

 

To add the foreign key:

 

ALTER TABLE Employees ADD CONSTRAINT fk_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

 

Explanation

 

This command ensures that each DepartmentID in the Employees table corresponds to an ID in the Departments table.

Dropping Foreign Key Constraints

If the foreign key relationship is no longer required, we can drop it.

 

Syntax

 

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

 

Example

 

To drop the foreign key from Employees table:

 

ALTER TABLE Employees DROP CONSTRAINT fk_Employees_DepartmentID;

 

Explanation

 

ALTER command in SQL removes the foreign key constraint from the DepartmentID column in the Employees table.

Adding Unique Key Constraints

A unique key constraint ensures all values in a column are unique.

 

Syntax

 

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

 

Example

 

To add a unique constraint to the Email column in our Employees table:

 

ALTER TABLE Employees ADD CONSTRAINT unique_Email UNIQUE (Email);

 

Explanation

 

This command ensures that no two employees can have the same email address.

Dropping Unique Key Constraints

We can drop a unique key constraint if it is no longer needed.

 

Syntax

 

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

 

Example

 

To drop the unique constraint from the Email column:

 

ALTER TABLE Employees DROP CONSTRAINT unique_Email;

 

Explanation

 

This command removes the unique key constraint, allowing duplicate email addresses.

Adding Check Constraints

Check constraints ensure that all values in a column satisfy a specific condition.

 

Syntax

 

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

 

Example

 

To add a check constraint to ensure the Age is at least 18:

 

ALTER TABLE Employees ADD CONSTRAINT check_Age CHECK (Age >= 18);

 

Explanation

 

ALTER command in SQL ensures that all employees are at least 18 years old.

Dropping Check Constraints

We can remove a check constraint if it’s no longer required.

 

Syntax

 

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

 

Example

 

To drop the check constraint on Age:

 

ALTER TABLE Employees DROP CONSTRAINT check_Age;

 

Explanation

 

This command removes the check constraint, allowing any age value.

Adding and Dropping Indexes

Indexes are essential for improving the speed of data retrieval in SQL databases. Using the ALTER TABLE statement, we can add and drop indexes to enhance query performance.

Adding Indexes

We use the ADD INDEX clause to add an index. This helps speed up searches on a specific column or a set of columns.

Syntax for Adding an Index

 

ALTER TABLE table_name ADD INDEX index_name (column_name);

 

Example

 

Suppose we have an Employees table:

 

ID FullName Age Email Phone
1 John 30 john@example.com 123456789
2 Alice 28 alice@example.com 987654321
3 Bob 25 bob@example.com 555555555

 

We want to add an index to the Email column to speed up email searches:

 

ALTER TABLE Employees ADD INDEX idx_email (Email);

 

Explanation

 

This command creates an index named idx_email on the Email column. This improves the performance of queries that search by email.

Dropping Indexes

When an index is no longer needed, we can remove it using the DROP INDEX clause.

 

Syntax for Dropping an Index

 

ALTER TABLE table_name DROP INDEX index_name;

 

Example

 

To drop the index idx_email from the Employees table:

 

ALTER TABLE Employees DROP INDEX idx_email;

 

Explanation

 

This command removes the idx_email index from the Email column, reversing the indexing process.

Common Mistakes to Avoid When Using the ALTER Command

  • Misnaming Columns or Tables: Most importantly, when naming the columns and the tables, ensure that they are spelt correctly and there is no typing error.
  • Incorrect Data Types: Make sure you select a data type that is suitable for storing the data you need in your database.
  • Neglecting Constraints: Do not forget to include necessary constraint adornments to ensure data consistency.
  • Forgetting to Back Up Data: Never design any structural changes before taking the backup of all the data.
  • Not Checking Database-Specific Limitations: Be aware of the limitations and specific syntax for your database system.

Conclusion

In this blog on ALTER command in SQL, we learned how we can modify fundamental characteristics connected with the tables in the databases without making new ones. Next, we explored where and how to include a new column, modify the existing one, and remove the unnecessary columns when required. In this way, you can change the name of the specific column or table for the purpose of making the management of the overall database environment and its contents more clear.

 

Further, we analysed such operations as changing the data type and working with constraints: primary and foreign keys, unique, and check constraints. We also discussed potential and actual index creations and deletions to enhance query responses more. As learned throughout this blog, the various ALTER command techniques provide the necessary tools to enable the necessary changes to the database structures, thus making them stronger, flexible, and well-organised.

FAQs
The ALTER command modifies the structure of an existing database table. We can add, delete, or change columns and manage constraints and indexes.
Yes, we can rename a table using the query:
ALTER TABLE table_name RENAME TO new_table_name;
 
To add multiple columns, we use:
ALTER TABLE table_name ADD (column1 datatype, column2 datatype, ...);
 
In MySQL, the syntax to change a column's data type is:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Not all database systems support dropping columns, so it's essential to check your system's specific limitations. For example, older versions of SQLite do not support this operation.

Deploying Applications Over the Cloud Using Jenkins

Prashant Kumar Dey

Prashant Kumar Dey

Associate Program Director - Hero Vired

Ex BMW | Google

24 October, 7:00 PM (IST)

Limited Seats Left

Book a Free Live Class

left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.
Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

|

Sitemap

© 2024 Hero Vired. All rights reserved