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: 0I 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
You can download the full source code of this example here: SQL Add Column Example
