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 AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)