ALTER Command in SQL – A Quick Guide

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

Example

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

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

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:

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

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

Syntax for Adding Multiple Columns

Example

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

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:

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:

• MySQL / Oracle:

• PostgreSQL:

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:

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

Example

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

Explanation

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

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

Example

We want to rename the Name column to FullName:

Explanation

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

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

Example

If we want to rename the Employees table 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.

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:

• MySQL / Oracle:

• PostgreSQL:

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:

We can modify the Phone column like this:

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

Example

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

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

Example

To drop the primary key from the Employees table:

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

Example

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

To add the foreign key:

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

Example

To drop the foreign key from Employees table:

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

Example

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

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

Example

To drop the unique constraint from the Email column:

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

Example

To add a check constraint to ensure the Age is at least 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

Example

To drop the check constraint on 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

Example

Suppose we have an Employees table:

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

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

Example

To drop the index idx_email from the Employees table:

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.