olaoyesunday Posted January 8, 2020 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. Link to comment Share on other sites More sharing options...
Larry Posted January 8, 2020 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. Link to comment Share on other sites More sharing options...
olaoyesunday Posted January 14, 2020 Author 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; Link to comment Share on other sites More sharing options...
Larry Posted January 26, 2020 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. Link to comment Share on other sites More sharing options...
kjosiah9 Posted April 27, 2020 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; Link to comment Share on other sites More sharing options...
Larry Posted April 27, 2020 Share Posted April 27, 2020 You're using single quotes when those should be backticks. Link to comment Share on other sites More sharing options...
kjosiah9 Posted April 29, 2020 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 Link to comment Share on other sites More sharing options...
Larry Posted April 29, 2020 Share Posted April 29, 2020 Make sure you create the users table before you create the orders table. Link to comment Share on other sites More sharing options...
Recommended Posts