Prev Next
SQL ALTER TABLE statement can be used to rename a table as well as to add, modify and delete columns of the table. The SQL ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax for various SQL ALTER TABLE is given below:
1. To add a column:
ADD is used to add columns into the existing table. Sometimes we may require to add additional information, in that case we do not require to create the whole database again, ADD comes to our rescue.
Syntax:
ALTER TABLE table_name ADD column_name data_type;
Example:
ALTER TABLE student ADD Address VARCHAR(255);
2. To modify a column:
It is used to modify the existing columns in a table. Multiple columns can also be modified at once.
Syntax:
ALTER TABLE table_name MODIFY column_name column_type;
Example:
ALTER TABLE student MODIFY Student_ID int NOT NULL;
3. To drop or delete a column:
DROP COLUMN is used to drop column in a table. Deleting the unwanted columns from the table.
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE student DROP COLUMN City;
4. To rename a column:
Syntax:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
Example:
ALTER TABLE student RENAME COLUMN Address to FULL_ADDRESS;
5. To rename a table:
Syntax:
RENAME TABLE existing_table_name TO new_table_name
Example:
RENAME TABLE student TO student_A