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:
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.
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 EtudiantsADD 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 ], ...
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 ]
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 EtudiantsADD prenom VARCHAR(45)AFTER id;ALTER TABLE EtudiantsCHANGE COLUMN nom postnom VARCHAR(40);DESC Etudiants;
Note: If we have the latest version of
MySQL
, we can useALTER TABLE
withRENAME
.ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name
To delete an existing column from our table, we’ll use ALTER TABLE
with DROP
.
ALTER TABLE table_name
DROP COLUMN column_name;
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 EtudiantsADD prenom VARCHAR(45)AFTER id;ALTER TABLE EtudiantsCHANGE COLUMN nom postnom VARCHAR(40);ALTER TABLE EtudiantsDROP COLUMN sexe;DESC Etudiants;
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 columnDROP COLUMN column_name
to delete a column