olaoyesunday 0 Posted January 8, 2020 Report Share Posted January 8, 2020 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. Quote Link to post Share on other sites
Larry 428 Posted January 8, 2020 Report Share Posted January 8, 2020 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. Quote Link to post Share on other sites
olaoyesunday 0 Posted January 14, 2020 Author Report Share Posted January 14, 2020 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; Quote Link to post Share on other sites
Larry 428 Posted January 26, 2020 Report Share Posted January 26, 2020 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. Quote Link to post Share on other sites
kjosiah9 0 Posted April 27, 2020 Report Share Posted April 27, 2020 Dear all, I get this Error message when I enter in the code from page 53 and 54. Everything appears to be exact: CREATE TABLE 'categories' ( 'id' SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 'Category' VARCHAR(45) NOT NULL, PRIMARY KEY ('id'), UNIQUE INDEX 'category_UNIQUE' ('category' ASC) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE 'orders' ( 'id' INT UNSIGNED NOT NULL AUTO_INCREMENT, 'users_id' INT UNSIGNED NOT NULL, 'transaction_id' VARCHAR(45) NOT NULL, 'payment_status' VARCHAR(45) NOT NULL, 'payment_amount' INT UNSIGNED NOT NULL, 'date_created' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ('id'), INDEX 'date_created' ('date_created' ASC), INDEX 'transaction_id' ('transaction_id' ASC), CONSTRAINT 'fk_orders_users1' FOREIGN KEY ('id') REFERENCES 'users' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION )ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE 'pages' ( 'id' INT UNSIGNED NOT NULL AUTO_INCREMENT, 'categories_id' SMALLINT UNSIGNED NOT NULL, 'title' VARCHAR(100) NOT NULL, 'description' TINYTEXT NOT NULL, 'content' LONGTEXT NULL, 'date_created' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ('id'), INDEX 'date_created' ('date_created' ASC), INDEX 'fk_pages_categories_idx' ('categories_id' ASC), CONSTRAINT 'fk_pages_categories' FOREIGN KEY ('categories_id') REFERENCES 'categories' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE 'pdfs' ( 'id' INT UNSIGNED NOT NULL AUTO_INCREMENT, 'title' VARCHAR(100) NOT NULL, 'description' TINYTEXT NOT NULL, 'tmp_name' CHAR(63) NOT NULL, 'file_name' VARCHAR(100) NOT NULL, 'size' MEDIUMINT UNSIGNED NOT NULL, 'date_created' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ('id'), UNIQUE INDEX 'tmp_name_UNIQUE' ('tmp_name' ASC), INDEX 'date_created' ('date_created' ASC) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE 'users' ( 'id' INT UNSIGNED NOT NULL AUTO_INCREMENT, 'type' ENUM('member','admin') NOT NULL DEFAULT 'member', 'username' VARCHAR(45) NOT NULL, 'email' VARCHAR(80) NOT NULL, 'pass' VARCHAR(255) NOT NULL, 'first_name' VARCHAR(45) NOT NULL, 'last_name' VARCHAR(45) NOT NULL, 'date_created' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 'date_expires' DATE NOT NULL, 'date_modified' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY ('id'), UNIQUE INDEX 'username_UNIQUE' ('username' ASC), UNIQUE INDEX 'email_UNIQUE' ('email' ASC), INDEX 'login' ('email' ASC, 'pass' ASC) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; Quote Link to post Share on other sites
Larry 428 Posted April 27, 2020 Report Share Posted April 27, 2020 You're using single quotes when those should be backticks. Quote Link to post Share on other sites
kjosiah9 0 Posted April 29, 2020 Report Share Posted April 29, 2020 Wow, my attention to detail needs refinement. that you for the critique. Thank you for your guidance, however when I enter the changes this happens bellow: CREATE TABLE `orders` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `users_id` INT UNSIGNED NOT NULL, `transaction_id` VARCHAR(45) NOT NULL, `payment_status` VARCHAR(45) NOT NULL, `payment_amount` INT UNSIGNED NOT NULL, `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX `date_created` (`date_created` ASC), INDEX `transaction_id` (`transaction_id` ASC), CONSTRAINT `fk_orders_users1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION )ENGINE = InnoDB DEFAULT CHARSET=utf8; Warm regards, Kervin Quote Link to post Share on other sites
Larry 428 Posted April 29, 2020 Report Share Posted April 29, 2020 Make sure you create the users table before you create the orders table. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.