What does the ALTER command do in SQL?

The ALTER command in SQL is a powerful tool used to modify the structure of database tables. It allows users to add, delete, or modify columns, as well as rename tables and columns. Understanding the various uses of the ALTER command is essential for managing and optimizing database structures. In this article, we will explore the different functions of the ALTER command and how it can be used to enhance database performance and maintainability.

The primary functions of the ALTER command include:

1. Adding columns to an existing table:
One of the most common uses of the ALTER command is to add new columns to an existing table. This can be done using the following syntax:

“`sql
ALTER TABLE table_name
ADD column_name data_type constraints;
“`

For example, to add a new column named “age” of type INT to a table named “employees”, you would use the following command:

“`sql
ALTER TABLE employees
ADD age INT;
“`

2. Deleting columns from an existing table:
The ALTER command can also be used to remove columns from a table. To delete a column, use the following syntax:

“`sql
ALTER TABLE table_name
DROP COLUMN column_name;
“`

For instance, to delete the “age” column from the “employees” table, you would execute:

“`sql
ALTER TABLE employees
DROP COLUMN age;
“`

3. Modifying column properties:
You can modify the data type, size, or constraints of a column using the ALTER command. The syntax for modifying a column is as follows:

“`sql
ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type constraints;
“`

For example, to change the data type of the “age” column in the “employees” table to VARCHAR(10), you would use:

“`sql
ALTER TABLE employees
MODIFY COLUMN age VARCHAR(10);
“`

4. Renaming tables and columns:
The ALTER command can also be used to rename tables and columns. To rename a table, use the following syntax:

“`sql
ALTER TABLE old_table_name
RENAME TO new_table_name;
“`

To rename a column, use the following syntax:

“`sql
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type constraints;
“`

For instance, to rename the “employees” table to “staff”, you would execute:

“`sql
ALTER TABLE employees
RENAME TO staff;
“`

And to rename the “age” column to “years_old” in the “staff” table, you would use:

“`sql
ALTER TABLE staff
CHANGE age years_old VARCHAR(10);
“`

In conclusion, the ALTER command in SQL is a versatile tool that allows users to modify the structure of database tables. By adding, deleting, or modifying columns, renaming tables and columns, and adjusting column properties, users can optimize their database structures and improve database performance. Familiarizing yourself with the various functions of the ALTER command is crucial for efficient database management.