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:
In the above,
Sometimes it is required to add multiple columns into the existing table. Then, we can use the syntax as follows:
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:
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.
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.
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:
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:
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:
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:
In the above,
MySQL RENAME COLUMN Example
This example shows how we can change the column name in the MySQL table:
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:
In the above,
If we want to remove multiple columns from the table, execute the following statements:
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:
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:
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: