Jump to content
Larry Ullman's Book Forums

Recommended Posts

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: Documentation

#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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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(32NOT 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;

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...