How do I alter a MySQL table column defaults?

To alter a MySQL table column defaults, you can use the CHANGE command. The syntax is as follows −

alter table yourTableName change yourCoumnName youColumnName datatype not null default Value;

To understand the above syntax, let us create a table. The following is the query −

mysql> create table DefaultDemo
   −> (
   −> ArrivalTime timestamp
   −> );
Query OK, 0 rows affected (0.65 sec)

Here is the query that describes the table with default column −

mysql> desc DefaultDemo;

The following is the output −

+-------------+-----------+------+-----+---------+-------+
| Field       | Type      | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| ArrivalTime | timestamp | YES  |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
1 row in set (0.11 sec)

Now you can alter the MySQL table column for default value. The query is as follows −

mysql> ALTER TABLE DefaultDemo CHANGE ArrivalTime ArrivalTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (1.71 sec)
Records: 0 Duplicates: 0 Warnings: 0

Check whether the default value got inserted or not with the help of insert command. The query is as follows −

mysql> insert into DefaultDemo values();
Query OK, 1 row affected (0.15 sec)

Display the records −

mysql> select *from DefaultDemo;

The following is the output −

+---------------------+
| ArrivalTime         |
+---------------------+
| 2018-12-07 11:31:00 |
+---------------------+
1 row in set (0.00 sec)

Updated on: 30-Jul-2019

126 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements