What does alter table clause do?
The “ALTER TABLE” clause in SQL is a powerful statement used to modify the structure of a table in a database. This clause allows users to add, delete, or modify columns, as well as to rename tables and columns. By using the “ALTER TABLE” clause, database administrators and developers can efficiently manage their database schema to meet the evolving needs of their applications. In this article, we will explore the various operations that can be performed using the “ALTER TABLE” clause and provide examples to illustrate its usage.
The primary purpose of the “ALTER TABLE” clause is to modify the structure of a table. This can include adding new columns, deleting existing columns, modifying the data type of columns, and renaming columns or the table itself. Let’s take a closer look at each of these operations.
Adding a Column
Adding a new column to an existing table is a common use case for the “ALTER TABLE” clause. This can be done using the following syntax:
“`sql
ALTER TABLE table_name
ADD column_name column_type;
“`
For example, to add a “salary” column of type “DECIMAL(10, 2)” to an “employees” table, you would use the following SQL statement:
“`sql
ALTER TABLE employees
ADD salary DECIMAL(10, 2);
“`
Deleting a Column
Deleting a column from a table is another operation that can be performed using the “ALTER TABLE” clause. This is done using the “DROP COLUMN” keyword:
“`sql
ALTER TABLE table_name
DROP COLUMN column_name;
“`
For instance, if you want to remove the “department_id” column from the “employees” table, you would execute the following SQL statement:
“`sql
ALTER TABLE employees
DROP COLUMN department_id;
“`
Modifying a Column
Modifying the data type or other properties of a column is also possible with the “ALTER TABLE” clause. This operation is achieved using the “ALTER COLUMN” keyword:
“`sql
ALTER TABLE table_name
ALTER COLUMN column_name new_column_type;
“`
For example, to change the data type of the “salary” column in the “employees” table to “VARCHAR(255)”, you would use the following SQL statement:
“`sql
ALTER TABLE employees
ALTER COLUMN salary VARCHAR(255);
“`
Renaming a Column or Table
In addition to modifying the structure of a table, the “ALTER TABLE” clause can also be used to rename columns or the table itself. To rename a column, use the following syntax:
“`sql
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
“`
For instance, to rename the “salary” column in the “employees” table to “wage”, you would execute the following SQL statement:
“`sql
ALTER TABLE employees
RENAME COLUMN salary TO wage;
“`
To rename the table, use the following syntax:
“`sql
ALTER TABLE old_table_name
RENAME TO new_table_name;
“`
For example, to rename the “employees” table to “staff”, you would use the following SQL statement:
“`sql
ALTER TABLE employees
RENAME TO staff;
“`
In conclusion, the “ALTER TABLE” clause is a versatile tool for managing the structure of a database table. By using this clause, users can add, delete, or modify columns, as well as rename tables and columns. Understanding how to use the “ALTER TABLE” clause is essential for database administrators and developers who need to maintain and evolve their database schema.
