MySQL Alter Table Add, Drop and Modify Column

MySQL ALTER TABLE statement changes the structure of a table. The MySQL Alter Table statement is used to add, delete, rename, or modify columns in an existing database table. The ALTER TABLE statement is also responsible to perform add and drop various constraints on an existing table. You can also change characteristics such as the storage engine used for the table or the table comment.

ALTER TABLE - ADD New Column

To add a new column in existing table, use the following syntax:

ALTER TABLE table_name
ADD columnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the new column that you want to add to the table.

datatype: The data type of the column (NULL or NOT NULL, etc).

ALTER TABLE ADD New Column Example

ALTER TABLE tblCustomerLoan
ADD phoneNo INT NOT NULL;

Note:

In the above example, the new column, "phoneNo", is of type INT and is going to hold a integer data.

The NOT NULL is a field attribute and it is used to make sure field should not be NULL.

You can use MySQL Command Line Client to alter existing table and new column. It will look like this:

Show NEWLY ADDED COLUMN

DESCRIBE tblCustomerLoan;  

You can use MySQL Command Line Client to show newly added column. It will look like this:

ALTER TABLE - Add Multiple Columns in the Table

To add multiple new column in existing table, use the following syntax:

ALTER TABLE table_name
ADD columnname datatype
[ FIRST | AFTER columnname ],
ADD columnname datatype 
[ FIRST | AFTER columnname ],
.... 
;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the new column that you want to add to the table.

datatype: The data type of the column (NULL or NOT NULL, etc).

ALTER TABLE ADD New Multiple Columns Example

ALTER TABLE tblCustomerLoan
ADD aadharNo INT NOT NULL
AFTER phoneNo,
ADD panNo INT NOT NULL
AFTER aadharNo;

Note:

In the above example, the new column "aadharNo" is of type INT and is going to hold a integer data and will be add after phoneNo column. The another column "panNo" is of type INT and is going to hold a integer data and will be add after aadharNo column.

The NOT NULL is a field attribute and it is used to make sure field should not be NULL.

You can use MySQL Command Line Client to alter existing table and multiples new columns. It will look like this:

Show NEWLY ADDED MULTIPLE COLUMNS

DESCRIBE tblCustomerLoan;  

You can use MySQL Command Line Client to show newly added column. It will look like this:

ALTER TABLE - MODIFY Column in the Table

To change the data type of the column in existing table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN columnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the existing column that you want to modify to the table.

datatype: The modified data type of the column.

ALTER TABLE ADD New Multiple Columns Example

ALTER TABLE tblCustomerLoan
MODIFY COLUMN address VARCHAR(200) NOT NULL;

Note:

In the above example, we increase the "address" column size from 150 to 200 of type VARCHAR and is going to hold a VARCHAR data. Also forcing "address" column not to store NULL value into the column.

You can use MySQL Command Line Client to alter existing table and modify exising column. It will look like this:

Show MODIFIED COLUMN

DESCRIBE tblCustomerLoan;  

You can use MySQL Command Line Client to show modified column. It will look like this:

ALTER TABLE - DROP Column in Table

To delete/remove column in the existing table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN columnname;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the existing column that you want to modify to the table.

ALTER TABLE DROP Column Example

ALTER TABLE tblCustomerLoan
DROP COLUMN phoneNo;

Note:

In the above example, we drop/delete the "phoneNo" column from table.

You can use MySQL Command Line Client to drop/delete column "phoneNo" from tabl. It will look like this:

Show DROP COLUMN

DESCRIBE tblCustomerLoan;  

You can use MySQL Command Line Client to show droped/deleted column. It will look like this:

ALTER TABLE - RENAME Column in Table

To rename column in the existing table, use the following syntax:

ALTER TABLE table_name
CHANGE COLUMN oldcolumnname newcolumnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

oldcolumnname: The name of the existing column that you want to modify to the table.

newcolumnname: The new name of the existing column that you want to modify to the table.

datatype: The modified data type of the column.

ALTER TABLE RENAME Column Name Example

ALTER TABLE tblCustomerLoan
CHANGE COLUMN phoneNo cellNo INT;

Note:

In the above example, we rename the existing column "phoneNo" to "cellNo" in table.

You can use MySQL Command Line Client to rename column "phoneNo" to "cellNo" in table. It will look like this:

Show RENAME COLUMN

DESCRIBE tblCustomerLoan;  

You can use MySQL Command Line Client to show droped/deleted column. It will look like this: