SQL ALTER Command

The SQL Alter command is used in altering the table structure as follows;

• It is used to add a column to an existing table
• It is used to rename any existing column
• It is used to change the datatype of any column or to modify its size.
• It is used to drop a column from the table.

ALTER Command: Add a new Column

With Alter command, you can add a column to an existing table. This can be done with the syntax shown below;

ALTER TABLE table_name ADD( column_name datatype);

Example:

ALTER TABLE student ADD(
    address VARCHAR(200)
);

The command above can be used to add a new column address to the table student, that can hold data of type varchar which is nothing but a string, of length 200.

ALTER Command: Add multiple new Columns

With the use of Alter command, you can even add multiple new columns to any existing table. This can be done with the syntax shown below;

ALTER TABLE table_name ADD( 

column_name1 datatype1, 

column-name2 datatype2, 

column-name3 datatype3

);

Example:

ALTER TABLE student ADD(
    father_name VARCHAR(60), 
    mother_name VARCHAR(60), 
    dob DATE); 

The command above can be used to add three new columns to the student table

ALTER Command: Add Column with default value

The Alter command is typically used to add a new column to an existing table with a default value too. Then you can use the default value when no value is inserted in the column. This can be done with the syntax shown below;

ALTER TABLE table_name ADD( 

column-name1 datatype1 DEFAULT some_value 

);

Example:

ALTER TABLE student ADD(
    dob DATE DEFAULT '16-Mar-22'
);

The command above can be used to add a new column with a preset default value to the table student.

ALTER Command: Modify an existing Column

The Alter command can be used in modifying the data type of any existing column. This can be done with the syntax shown below;

ALTER TABLE table_name modify( 

column_name datatype

 );

Example:

ALTER TABLE student MODIFY(
    address varchar(255)); 

Don’t forget, we added a new column address in the beginning? The command above will modify the address column of the student table, to now hold up to 300 characters.

ALTER Command: Rename a Column

The Alter command can be used to rename an existing column. This can be done with the syntax shown below;

ALTER TABLE table_name RENAME old_column_name TO new_column_name;

Example:

ALTER TABLE student RENAME 
    address TO location; 

The command above is used to rename the address column to location.

ALTER Command: Drop a Column

With the Alter command, you can drop or remove columns. This can be done with the syntax shown below

ALTER TABLE table_name DROP( column_name);

Example for drop a Column

ALTER TABLE student DROP(
    address); 

The command above will drop the address column from the table student.