SQL Problems

[olaoyesunday 0]

Dear all.

 I am having SQL issues with Table Carts and Wish_lists   table on page 189, 192. the date_modified column was given error it was saying 

MySQL said:

#1067 - Invalid default value for 'date_modified' should I just remove TIMESTAMP Data type from date_modified column and change it to DATETIME?. I am using Maria DB 10.3.15 version bundled with xampp.

[Larry 422]

I've reviewed the MariaDB documentation for timestamp: https://mariadb.com/kb/en/timestamp/

It looks like the first timestamp column in a table has different behavior than the second. We want date_created to be automatically set on creation, but not update. We want date_modified to be automatically updated on each change. To get that behavior you'll need to change the DB and the SQL queries. Assuming we want both the date_modified and the date_created, I'd be inclined to put the date_modified column first and then populate date_created to the current timestamp upon INSERT. 

Or just drop the date_created column entirely and update the SQL commands accordingly.

[olaoyesunday 0]

Upon re-written of the SQL below I got this  error: #1067 - Invalid default value for 'date_modified'

but When I removed "DEFAULT '0000-00-00 00:00:00' " the SQL ran successfully. I want to know if I am correct or not but  Based on the link you sent I think I am on the way " For the first column that uses the TIMESTAMP data type in a specific table,
MariaDB automatically assigns the following properties to the column:
• DEFAULT CURRENT_TIMESTAMP
• ON UPDATE CURRENT_TIMESTAMP. 

 

 

CREATE TABLE `carts` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `user_session_id` CHAR(32) NOT NULL,

  `product_type` enum('coffee','goodies') NOT NULL,

  `product_id` MEDIUMINT(8) UNSIGNED NOT NULL,

  `quantity` TINYINT(3) UNSIGNED NOT NULL,

  `date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

` date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  KEY `product_type` (`product_type`,`product_id`),

  KEY `user_session_id` (`user_session_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

[Larry 422]

I played around with this a bit. Again, I might entirely rethinking the logic, but assuming we want to keep two date columns, this create works:

CREATE TABLE `carts` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_session_id` CHAR(32) NOT NULL,
  `product_type` enum('coffee','goodies') NOT NULL,
  `product_id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `quantity` TINYINT(3) UNSIGNED NOT NULL,
  `date_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_type` (`product_type`,`product_id`),
  KEY `user_session_id` (`user_session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I put `date_created` second and changed it to a DATETIME and then it can use the CURRENT_TIMESTAMP as the default so the INSERT query doesn't need to be changed.