How to add, rename, or delete a column in SQL

Overview

While working on database projects, we often need to edit the columns of our tables in SQL. In this shot, we cover the following processes:

  • Adding a new column
  • Renaming an existing column
  • Deleting an existing column

Adding a new column

The syntax to add a new column to a table is as follows:

ALTER TABLE table_name
  ADD new_column_name column_definition
    [ FIRST | AFTER column_name ]

As we can see in the syntax above, we can specify the position where we want to add the new column.

Note: The keywords FIRST | AFTER are in brackets. This means that it is not mandatory to use them.

Example

Let’s use this syntax in a concrete example.

Here’s our initial table.

CREATE TABLE Etudiants(
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
sexe VARCHAR(1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DESC Etudiants;

Let’s press the Run button above to see the table fields.

Our goal is to add the prenom field just after id.

CREATE TABLE Etudiants(
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
sexe VARCHAR(1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE Etudiants
ADD prenom VARCHAR(45)
AFTER id
;
DESC Etudiants;

Note:

  • If our table already has content, the newly created column will be filled in with NULL. We can use UPDATE statement to add the missing content.
  • To add a default value while creating a new column, we can use the default keyword:
ALTER TABLE table_name
 ADD new_column_name column_definition default "value"
  [ FIRST | AFTER column_name ]
  • To add more than one column at once, we use this syntax:
ALTER TABLE table_name
 ADD new_column_name column_definition
  [ FIRST | AFTER column_name ],
 ADD new_column_name column_definition
  [ FIRST | AFTER column_name ],
 ...

Renaming an existing column

To rename an existing column on our table, we use ALTER TABLE with CHANGE.

ALTER TABLE table_name
  CHANGE COLUMN old_name new_name 
    column_definition
    [ FIRST | AFTER column_name ]

Example

Let’s rename the field nom to postnom.

CREATE TABLE Etudiants(
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
sexe VARCHAR(1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE Etudiants
ADD prenom VARCHAR(45)
AFTER id
;
ALTER TABLE Etudiants
CHANGE COLUMN nom postnom VARCHAR(40);
DESC Etudiants;

Note: If we have the latest version of MySQL, we can use ALTER TABLE with RENAME.

ALTER TABLE table_name
 RENAME COLUMN old_column_name  TO new_column_name

Deleting an existing column

To delete an existing column from our table, we’ll use ALTER TABLE with DROP.

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let’s drop the column sexe.

CREATE TABLE Etudiants(
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
sexe VARCHAR(1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE Etudiants
ADD prenom VARCHAR(45)
AFTER id
;
ALTER TABLE Etudiants
CHANGE COLUMN nom postnom VARCHAR(40);
ALTER TABLE Etudiants
DROP COLUMN sexe;
DESC Etudiants;

Conclusion

In this shot, we learned how to use ALTER TABLE to add, rename and delete a column in a table.

Let’s quickly recall the main points discussed so far. Just after ALTER TABLE table_name, we use:

  • ADD new_column_name column_definition to add a new column
  • CHANGE COLUMN old_name new_name column_definition or RENAME COLUMN old_column_name TO new_column_name to rename a column
  • DROP COLUMN column_name to delete a column

Free Resources