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.

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

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

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.

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.

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