How can we add columns with default values to an existing MySQL table?

While adding columns to an existing table with the help of ALTER command we can specify the default value also.

Syntax

Alter table table-name ADD (column-name datatype default data);

Example

In the example below, with the help of ALTER Command, column ‘City’ is added with default value ‘DELHI’ to the table ‘Student’.

mysql> Alter table Student ADD(City Varchar(10) Default 'DELHI');

Query OK, 5 rows affected (0.33 sec)
Records: 5 Duplicates: 0 Warnings: 0

Now from DESCRIBE command, we can check the default value of ‘City’ column.

mysql> describe Student\g

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| RollNO  | int(11)      | YES  |     | NULL    |       |
| Name    | varchar(20)  | YES  |     | NULL    |       |
| Class   | varchar(15)  | YES  |     | NULL    |       |
| Grade   | varchar(10)  | YES  |     | NULL    |       |
| Address | varchar(25)  | YES  |     | NULL    |       |
| Phone   | int(11)      | YES  |     | NULL    |       |
| Email   | varchar(20)  | YES  |     | NULL    |       |
| City    | varchar(10)  | YES  |     | DELHI   |       |
+---------+-------------+------+-----+---------+--------+

8 rows in set (0.04 sec)

Vikyath Ram

Published on 19-Feb-2018 08:32:28

• Related Questions & Answers
• How can we add multiple columns, with single command, to an existing MySQL table?
• How to add columns to an existing MySQL table?
• How can we add values into the columns of a MySQL table?
• How can we get a list of columns in an existing MySQL table?
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• How can we alter table to add MySQL virtual GENERATED COLUMNS?
• How can we alter table to add MySQL stored GENERATED COLUMNS?
• How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
• How to add current date to an existing MySQL table?
• How can we get more details about columns of an existing table than return by MySQL SHOW COLUMNS statement?
• How to add columns at specific position in existing table in MySQL?
• How can we delete an existing MySQL table by using PHP script?
• How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
• How can we create a table from an existing MySQL table in the database?
• How can we modify an existing MySQL event?