SQL ALTER TABLE Statement
SQL ALTER TABLE Statement
The SQL ALTER TABLE statement is used to modify an existing table in a database. The ALTER TABLE statement can be used to add, modify or delete columns, constraints, indexes, and more. The syntax for the ALTER TABLE statement is as follows:
ALTER TABLE table_name
[ADD|DROP|MODIFY] column_name data_type [optional_parameters];
Where table_name is the name of the table that you want to modify, ADD|DROP|MODIFY specifies the type of modification that you want to make, column_name is the name of the column that you want to add, modify or delete, data_type is the data type of the column, and optional_parameters specifies any optional parameters for the column.
For example, let’s say we have a table named “employees” with three columns: “id”, “first_name”, and “last_name”. We can use the following ALTER TABLE statements to modify the table structure:
- Add a new column:
ALTER TABLE employees
ADD email VARCHAR(100) NOT NULL UNIQUE;
This statement adds a new column named “email” with a data type of VARCHAR(100), and specifies that it cannot be null and must contain unique values.
- Modify an existing column:
ALTER TABLE employees
MODIFY email VARCHAR(255);
This statement modifies the data type of the “email” column from VARCHAR(100) to VARCHAR(255).
- Delete a column:
ALTER TABLE employees
DROP COLUMN email;
This statement removes the “email” column from the “employees” table.
In addition to adding, modifying, or deleting columns, the ALTER TABLE statement can be used to modify constraints, indexes, and other table properties. It is important to note that any changes made using the ALTER TABLE statement can affect existing data in the table, so it is recommended to be cautious when using this statement and to take a backup of the table before making any changes.