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)

Jennifer Nicholas

Published on 11-Jan-2019 08:19:00

• Related Questions & Answers
• How do I alter table column datatype on more than 1 column at a time in MySql?
• Alter a table column from VARCHAR to NULL in MySQL
• How to alter the data type of a MySQL table’s column?
• How do I see what character set a MySQL database  / table / column is?
• Alter a MySQL column to be AUTO_INCREMENT?
• How do I see all foreign keys to a table column?
• How do I detect if a table exist in MySQL?
• How to use ALTER TABLE statement for changing the size of a column in MySQL?
• How do I modify a MySQL column to allow NULL?
• How do I remove a uniqueness constraint from a MySQL table?
• How to add column using alter in MySQL?
• How do I list all the columns in a MySQL table?
• How do I get the creation date of a MySQL table?
• How do I show unique constraints of a table in MySQL?
• How do I clone the structure of a table in MySQL?