Invalid Default value (Time Stamp) issue in Database(SQL)

Question

I'm try to create table as below, but get error:

Invalid default value for 'updated_at'

I don't know what to do...what's wrong in query.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `user_id` int(11) DEFAULT NULL,
 `title` varchar(255) NOT NULL,
 `slug` varchar(255) NOT NULL UNIQUE,
 `views` int(11) NOT NULL DEFAULT '0',
 `image` varchar(255) NOT NULL,
 `body` text NOT NULL,
 `published` tinyint(1) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Have you checked this https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime ? — Mahesh_Loya, Oct 24 '20 at 05:31
I would try wrapping the literal date with a conversion to a timestamp value. — wallyk, Oct 24 '20 at 05:31

score 0 · Accepted Answer · answered Oct 24 '20 at 08:53

According SQL standard '0000-00-00 00:00:00' is not valid value for timestamp field. You should to use NULL as default value fro updated_at column.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `user_id` int(11) DEFAULT NULL,
 `title` varchar(255) NOT NULL,
 `slug` varchar(255) NOT NULL UNIQUE,
 `views` int(11) NOT NULL DEFAULT '0',
 `image` varchar(255) NOT NULL,
 `body` text NOT NULL,
 `published` tinyint(1) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Also ON UPDATE CURRENT_TIMESTAMP part is not relevant for created_at field, but relevant for updated_at

Invalid Default value (Time Stamp) issue in Database(SQL)

1 Answers1