MySQL Add/Delete Column

A column is a series of cells in a table that may contain text, numbers, and images. Every column stores one value for each row in a table. In this section, we are going to discuss how to add or delete columns in an existing table.

How can we add a column in MySQL table?

MySQL allows the ALTER TABLE ADD COLUMN command to add a new column to an existing table. The following are the syntax to do this:

ALTER TABLE table_name   

    ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

In the above,

• First, we need to specify the table name.
• Next, after the ADD COLUMN clause, we have to specify the name of a new column along with its definition.
• Finally, we need to specify the FIRST or AFTER keyword. The FIRST Keyword is used to add the column as the first column of the table. The AFTER keyword is used to add a new column after the existing column. If we have not provided these keywords, MySQL adds the new column as the last column in the table by default.

Sometimes it is required to add multiple columns into the existing table. Then, we can use the syntax as follows:

ALTER TABLE table_name   

    ADD COLUMN column_name1 column_definition [FIRST|AFTER existing_column],  

    ADD COLUMN column_name2 column_definition [FIRST|AFTER existing_column];

MySQL ADD COLUMN Example

Let us understand it with the help of various examples. Here, we will create a table named “Test” using the following statements:

CREATE TABLE Test (  

    Stude_id int AUTO_INCREMENT PRIMARY KEY,  

    Name varchar(55) NOT NULL  

);

The table structure looks like the below image:

After creating a table, we want to add a new column named City to the Test table. Since we have not specified the new column position explicitly after the column name, MySQL will add it as the last column.

ALTER TABLE Test   

ADD COLUMN City VARCHAR(30) NOT NULL

Next, we want to add a new column named Phone_number to the Test table. This time, we will explicitly specify the new column position so that MySQL adds the column to the specified place.

ALTER TABLE Test   

ADD COLUMN Phone_number VARCHAR(20) NOT NULL AFTER Name;

In the below output, we can see that the two columns are added successfully at the specified position.

Let us add some data into the Test table using the INSERT statement as follows:

INSERT INTO Test( Name, Phone_number, City)   

VALUES ('Peter', '34556745362', 'California'),  

('Mike', '983635674562', 'Texas');

It will look like this.

Suppose we want to add more than one column ,(Branch, Email) in the Test table. In that case, execute the statement as follows:

ALTER TABLE Test   

ADD COLUMN Branch VARCHAR(30) DEFAULT NULL After Name,  

ADD COLUMN Email VARCHAR(20) DEFAULT NULL AFTER Phone_number;

It is to note that columns Branch and Email are assigned to default value NULL. However, the Test table already has data so that MySQL will use null values for those new columns.

We can verify the record in the Test table as below:

If we accidentally add a new column with the existing column name, MySQL will throw an error. For example, execute the below statement that issues an error:

ALTER TABLE Test   

ADD COLUMN City VARCHAR(30) NOT NULL

We will get the following error message.

How can we rename a column in MySQL table?

MySQL allows the ALTER TABLE CHANGE COLUMN statement to change the old column with a new name. The following are the syntax to do this:

ALTER TABLE table_name   

    CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];

In the above,

• First, we need to specify the table name.
• Next, after the CHANGE COLUMN clause, we have to specify the old column name and new column name along with its definition. We must have to specify the column definition even it will not change.
• Finally, we need to specify the FIRST or AFTER keyword. It is optional that specified when we need to change the column name at the specific position.

MySQL RENAME COLUMN Example

This example shows how we can change the column name in the MySQL table:

ALTER TABLE Test  

  CHANGE COLUMN Phone_number Mobile_number  

    varchar(20) NOT NULL;

This statement will change the column name Phone_number with the new name Mobile_number in the Test table. The below output explains it more clearly.

How can we drop a column from MySQL table?

Sometimes, we want to remove single or multiple columns from the table. MySQL allows the ALTER TABLE DROP COLUMN statement to delete the column from the table. The following are the syntax to do this:

ALTER TABLE table_name  DROP COLUMN column_name;

In the above,

• First, we need to specify the table name from which we want to remove the column.
• Next, after the DROP COLUMN clause, we have to specify the column name that we want to delete from the table. It is to note that the COLUMN keyword is optional in the DROP COLUMN clause.

If we want to remove multiple columns from the table, execute the following statements:

ALTER TABLE table_name  

DROP COLUMN column_1,  

DROP COLUMN column_2,  

......;

MySQL DROP COLUMN Example

This example explains how we can delete a column from the MySQL table. Here, we will take a table “Test” that we have created earlier and look like the below image:

Suppose we want to delete a column name “Branch” from the Test table. To do this, execute the below statement:

ALTER TABLE Test DROP COLUMN Branch;  

After successful execution, we can verify the result below where a column Branch is deleted from the table:

In some cases, it is required to remove multiple columns from the table. To do this, we need to execute the below statement:

ALTER TABLE Test  

    DROP COLUMN Mobile_number,  

    DROP COLUMN Email;

The command will delete both columns. We can verify it using the queries given in the below image.

Remember the following key points before deleting a column from the table:

MySQL works with relational databases where the schema of one table can depend on the columns of another table. So when we remove a column from one table, it will effects all dependent tables also. Consider the below points while removing column:

• When we remove columns from a table, it will affect all associated objects such as triggers, stored procedures, and views. Suppose we delete a column that is referencing in the trigger. After removing the column, the trigger becomes invalid.
• The dropped column depends on other applications code, must also be changed, which takes time and effort.
• When we remove a column from the large table, it will affect the database’s performance during removal time.