Modifications like adding, deleting in a existing table can be done using ALTER TABLE statement.
Various constraints can be added or deleted using ALTER TABLE statement
ADDING COLUMN IN THE TABLE
Syntax:
ALTER TABLE table_name
ADD column_name datatype;Example: To add Phone_Number column in the table students.
ALTER TABLE students
ADD Phone_Number int;This will add a column in the table namely Phone_Number, as shown below:
DROPING COLUMN FROM THE TABLE
To delete a column form the existing table we use the following syntax:
Note -> Some database systems don’t allow deleting a column.
Syntax:
ALTER TABLE table_name
DROP column_name;Example:
ALTER TABLE students
DROP Phone_Number;This will delete a column from the table namely Phone_Number, and then the table will look like:
ALTERING/MODIFYING COLUMN IN THE EXISTING TABLE
The datatype of a column can be changed using the following syntax:
Syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;Example: To change Total_Marks column from integer to character.
ALTER TABLE students
ALTER COLUMN Total_Marks varchar(100);In above example, previously Total_Marks column was storing integer type of values, but now it will store characters upto 100.