In an existing database, I've age column (INT). Now I need to set it as dob (DATETIME).
I try doing so through PHPMyAdmin, giving CURRENT_TIMESTAMP as default value as defined by answer with 138 upvotes. However PHPMyAdmin is complaining #1067 - invalid default value for 'dob' as in attached screenshot:
Can someone please suggest why I'm getting that error and how to fix that?
This question is related to
mysql
phpmyadmin
The answer is
You can't set CURRENT_TIMESTAMP as default value with DATETIME.
But you can do it with TIMESTAMP.
See the difference here.
Words from this blog
The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.
This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.
The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
The best way for DateTime is use a Trigger:
/************ ROLE ************/
drop table if exists `role`;
create table `role` (
`id_role` bigint(20) unsigned not null auto_increment,
`date_created` datetime,
`date_deleted` datetime,
`name` varchar(35) not null,
`description` text,
primary key (`id_role`)
) comment='';
drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
on `role`
for each row
set new.`date_created` = now();
Set the type of the field as TIMESTAMP too.
You're getting that error because the default value current_time is not valid for the type DATETIME. That's what it says, and that's whats going on.
The only field you can use current_time on is a timestamp.
I don't think you can achieve that with mysql date. You have to use timestamp or try this approach..
CREATE TRIGGER table_OnInsert BEFORE INSERT ON `DB`.`table`
FOR EACH ROW SET NEW.dateColumn = IFNULL(NEW.dateColumn, NOW());
Source: Stackoverflow.com