Topics

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.