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)
Published on 19-Feb-2018 13:02:28
