sql

SQL Add Column Example

In this article, we will explain how to add a column in SQL.

1. Introduction

An application’s requirements change over time. Sometimes it needs to increase the size of a column to handle larger data, sometimes it needs a new column to handle additional information. SQL ALTER TABLE statement is used to add, delete, and modify a column, and generally, a table’s structure. In this example, I will demonstrate how to:

  • Add column
  • Edit column
  • Delete column

2. Technologies Used

The example code in this article was built and run using:

  • MySQL
  • SQL

3. Set up

In this step, I will display the demo_table‘s structure. Click here for SQL basic commands.

Describe demo_table

mysql> use mysqlDemo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> show tables;
+---------------------+
| Tables_in_mysqlDemo |
+---------------------+
| courseData          |
| demo_table          |
| studentData         |
| student_course      |
+---------------------+
4 rows in set (0.00 sec)

mysql>

mysql> desc demo_table;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| name      | varchar(100) | NO   |     | NULL    |                |
| direction | int          | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

The demo_table has three columns: id, name, and direction. I will change it in the next step.

4. Alter Table

4.1 Add Column

The SQL ALTER TABLE Add column statement has the following syntax:

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

In this step, I will add two new columns to demo_table:

  • new_column1 has a default value of 10.
  • new_column2 is nullable and after the NAME column.

add column

mysql> ALTER TABLE demo_table
    -> ADD new_column1  INT NOT NULL DEFAULT 10 ,
    -> ADD new_column2  VARCHAR(20)  NULL  AFTER NAME;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

I will verify demo_table by describing the table structure. You will see the two new columns added.

desc demo_table

mysql> desc demo_table;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| new_column2 | varchar(20)  | YES  |     | NULL    |                |
| direction   | int          | NO   |     | NULL    |                |
| new_column1 | int          | NO   |     | 10      |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

I will confirm the default value is set with a Select statement. You will see new_column1 has value 10.

select * from demo_table

mysql> select * from demo_table;
+----+-------+-------------+-----------+-------------+
| id | name  | new_column2 | direction | new_column1 |
+----+-------+-------------+-----------+-------------+
|  1 | mary  | NULL        |         1 |          10 |
|  2 | shan  | NULL        |         2 |          10 |
|  3 | zheng | NULL        |         3 |          10 |
+----+-------+-------------+-----------+-------------+
3 rows in set (0.00 sec)

4.2 Edit Column

The SQL ALTER TABLE rename column statement syntax is as the following:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

I will rename demo_table‘s direction column to type and verify with desc demo_table command.

rename column

mysql> ALTER TABLE demo_table RENAME COLUMN direction TO type;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc demo_table;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| new_column2 | varchar(20)  | YES  |     | NULL    |                |
| type        | int          | NO   |     | NULL    |                |
| new_column1 | int          | NO   |     | 10      |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

We can use the CHANGE command to edit the column’s name and type.

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

I will change the new_column2‘s name to phone and type to varchar(32).

change column

mysql> ALTER TABLE demo_table CHANGE new_column2 phone VARCHAR(32);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc demo_table;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| phone       | varchar(32)  | YES  |     | NULL    |                |
| type        | int          | NO   |     | NULL    |                |
| new_column1 | int          | NO   |     | 10      |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

4.3 Drop Column

The SQL ALTER TABLE drop column statement has the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

In this step, I will drop the new_column1 added earlier.

drop column

mysql> ALTER TABLE demo_table drop column new_column1;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verifying the table with the desc demo_table command.

desc demo_table

mysql> desc demo_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| phone | varchar(32)  | YES  |     | NULL    |                |
| type  | int          | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

5. Download the Source Code

Download
You can download the full source code of this example here: SQL Add Column Example

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button