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 423]

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 423]

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.

[kjosiah9 0]

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;

 

 

[Larry 423]

You're using single quotes when those should be backticks.

[kjosiah9 0]

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

[Larry 423]

Make sure you create the users table before you create the orders table.