ADDING A NEW COLUMN INTO A TABLE

Difficulty

Last Reviewed

Notes

Reference Link

Reviewed

Status

Done

Introduction to MySQL ADD COLUMN statement

To add a new column to an existing table, you use the ALTER TABLE … ADD COLUMN statement as follows:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type
[FIRST | AFTER existing_column];Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, provide the table name to which you want to add a new column after the ALTER TABLEclause.
Second, define the new column and its attributes after the ADD COLUMN clause. Note that COLUMN keyword is optional so you can omit it.
Third, specify the position of the new column in the table.

When adding a new column to a table, you can specify its position within the table. You can use the keyword FIRST if you want the new column to be positioned as the first column in the table.

Alternatively, you can use the AFTER existing_column clause to specify that you want to add a new column after an existing column.

If you do not explicitly specify the position of the new column, the statement will automatically add it as the last column in the table.

To add two or more columns to a table at the same time, you use multiple ADD COLUMN clauses like this:

ALTER TABLE table_name
ADD [COLUMN] new_column_name data_type [FIRST|AFTER existing_column],
ADD [COLUMN] new_column_name data_type [FIRST|AFTER existing_column],
...;Code language: SQL (Structured Query Language) (sql)

MySQL ADD COLUMN examples

Let’s look at examples of adding one or more columns to a table.

We’ll create a table called vendors with two columns id and name:

CREATE TABLE vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,l
    name VARCHAR(255)
);

1) Adding one column example

First, add a new column phone to the vendors table:

ALTER TABLE vendors
ADD COLUMN phone VARCHAR(15) AFTER name;

Because we specify the position of the phone column explicitly after the name column, the statement places the phone column after the name column.

Second, view the columns list of the vendor table using the DESC statement:

DESC vendors;

2) Adding a column as the last column

First, add a new column vendor_group to the vendors table:

ALTER TABLE vendors
ADD COLUMN vendor_group INT NOT NULL;Code language: SQL (Structured Query Language) (sql)

In this statement, we don’t specify the new column’s position so it adds the vendor_group column as the last column of the vendors table.

Second, view the vendors table:

DESC vendors;

Output:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int          | NO   | PRI | NULL    | auto_increment |
| name         | varchar(255) | YES  |     | NULL    |                |
| phone        | varchar(15)  | YES  |     | NULL    |                |
| vendor_group | int          | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3) Adding two columns example

First, insert some rows into the vendors table.

INSERT INTO vendors(name,phone,vendor_group)
VALUES('IBM','(408)-298-2987',1),
      ('Microsoft','(408)-298-2988',1);

Second, query the data of the vendors table:

SELECT
  id,
  name,
  phone,
  vendor_group
FROM
  vendors;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------+----------------+--------------+
| id | name      | phone          | vendor_group |
+----+-----------+----------------+--------------+
|  1 | IBM       | (408)-298-2987 |            1 |
|  2 | Microsoft | (408)-298-2988 |            1 |
+----+-----------+----------------+--------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Third, add two more columns email and hourly_rate to the vendors table using two ADDclauses:

ALTER TABLE vendors
ADD COLUMN email VARCHAR(100) NOT NULL,
ADD COLUMN hourly_rate decimal(10,2) NOT NULL;Code language: SQL (Structured Query Language) (sql)

Note that both email and hourly_rate columns are NOT NULL. However, the vendors table already has data. In this case, MySQL will use default values for these new columns.

Finally, retrieve data from the vendors table:

SELECT
  id,
  name,
  phone,
  vendor_group,
  email,
  hourly_rate
FROM
  vendors;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------+----------------+--------------+-------+-------------+
| id | name      | phone          | vendor_group | email | hourly_rate |
+----+-----------+----------------+--------------+-------+-------------+
|  1 | IBM       | (408)-298-2987 |            1 |       |        0.00 |
|  2 | Microsoft | (408)-298-2988 |            1 |       |        0.00 |
+----+-----------+----------------+--------------+-------+-------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the email column is populated with blank, not the NULL values. And the hourly_rate column is filled with 0.00.

4) Adding a column that already exists

If you add a column that already exists in the table, MySQL will issue an error. For example, if you execute the following statement:

ALTER TABLE vendors
ADD COLUMN vendor_group INT NOT NULL;Code language: SQL (Structured Query Language) (sql)

Output:

Error Code: 1060. Duplicate column name 'vendor_group'Code language: SQL (Structured Query Language) (sql)

For the table with a few columns, it is easy to see which columns are already there. However, it becomes more difficult for a big table with hundreds of columns.

In this case, you want to check whether a column exists in a table before adding it.

However, there is no statement like ADD COLUMN IF NOT EXISTS available. Fortunately, you can get this information from the columns table of the information_schema database as the following query:

SELECT
    IF(count(*) = 1, 'Exist','Not Exist') AS result
FROM
    information_schema.columns
WHERE
    table_schema = 'classicmodels'
        AND table_name = 'vendors'
        AND column_name = 'phone';Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| result    |
+-----------+
| Not Exist |
+-----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

In the WHERE clause, we passed three arguments: table schema or database, table name, and column name. We used the IF function to check whether the column exists or not.

5) Adding an auto-increment column

MySQL allows a table to have up to one auto-increment column and that column must be defined as a key. For example:

First, create a new table called contacts:

CREATE TABLE contacts(
   name VARCHAR(255) NOT NULL
);Code language: PHP (php)

Second, insert some rows into contacts table:

INSERT INTO contacts(name)
VALUES
   ('John'),
   ('Jane');Code language: JavaScript (javascript)

Third, retrieve the data from the contacts table:

SELECT * FROM contacts;

Output:

+------+
| name |
+------+
| John |
| Jane |
+------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Fourth, add id column as the auto-increment primary key column to the contacts table:

ALTER TABLE contacts
ADD COLUMN id INT AUTO_INCREMENT
PRIMARY KEY;

Fifth, show the contacts table:

DESC contacts;

Output:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| name  | varchar(255) | NO   |     | NULL    |                |
| id    | int          | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)Code language: PHP (php)

Finally, retrieve the data from the contacts table:

SELECT * FROM contacts;

Output:

+------+----+
| name | id |
+------+----+
| John |  1 |
| Jane |  2 |
+------+----+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that MySQL automatically generates value for the id column.

Summary

Use MySQL ADD COLUMN clause in the ALTER TABLE statement to add one or more columns to a table.