我从Workbench运行脚本。这是完整的脚本:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-模式mydb
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
-表
mydb
。 categories
DROP TABLE IF EXISTS `mydb`.`categories` ;
CREATE TABLE IF NOT EXISTS `mydb`.`categories` (
`categories_id` INT(5) UNSIGNED NOT NULL,
`categories_name` VARCHAR(32) NOT NULL,
`categories_image` VARCHAR(64) NULL,
`parent_id` INT(5) UNSIGNED NOT NULL,
`sort_order` INT(3) NULL,
`date_added` TIMESTAMP NOT NULL DEFAULT 0,
`last_modified` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`categories_id`),
INDEX `fk_categories_categories1_idx` (`parent_id` ASC),
CONSTRAINT `fk_categories_categories1`
FOREIGN KEY (`parent_id`)
REFERENCES `mydb`.`categories` (`categories_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 manufacturers
DROP TABLE IF EXISTS `mydb`.`manufacturers` ;
CREATE TABLE IF NOT EXISTS `mydb`.`manufacturers` (
`manufacturers_id` INT(5) UNSIGNED NOT NULL,
`manufacturers_name` VARCHAR(32) NOT NULL,
`date_added` TIMESTAMP NOT NULL DEFAULT 0,
PRIMARY KEY (`manufacturers_id`))
ENGINE = InnoDB;
-表
mydb
。 products
DROP TABLE IF EXISTS `mydb`.`products` ;
CREATE TABLE IF NOT EXISTS `mydb`.`products` (
`products_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`products_model` VARCHAR(20) NULL,
`products_price` DECIMAL(10,2) UNSIGNED NULL,
`products_weight` DECIMAL(4,2) UNSIGNED NULL,
`manufacturers_id` INT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`products_id`),
INDEX `fk_products_manufacturers1_idx` (`manufacturers_id` ASC),
CONSTRAINT `manufacturers_id`
FOREIGN KEY (`manufacturers_id`)
REFERENCES `mydb`.`manufacturers` (`manufacturers_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 categories_has_products
DROP TABLE IF EXISTS `mydb`.`categories_has_products` ;
CREATE TABLE IF NOT EXISTS `mydb`.`categories_has_products` (
`categories_id` INT(5) UNSIGNED NOT NULL,
`products_id` INT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`categories_id`, `products_id`),
INDEX `fk_categories_has_products_products_idx` (`products_id` ASC),
INDEX `fk_categories_has_products_categories_idx` (`categories_id` ASC),
CONSTRAINT `categories_id`
FOREIGN KEY (`categories_id`)
REFERENCES `mydb`.`categories` (`categories_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `products_id`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 products_description
DROP TABLE IF EXISTS `mydb`.`products_description` ;
CREATE TABLE IF NOT EXISTS `mydb`.`products_description` (
`products_id` INT(5) UNSIGNED NOT NULL,
`products_name` VARCHAR(64) NOT NULL,
`products_description` TEXT NULL,
`products_url` VARCHAR(255) NULL,
`products_viewed` INT(5) UNSIGNED NULL,
PRIMARY KEY (`products_id`),
UNIQUE INDEX `products_name_UNIQUE` (`products_name` ASC),
CONSTRAINT `products_id`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 customers
DROP TABLE IF EXISTS `mydb`.`customers` ;
CREATE TABLE IF NOT EXISTS `mydb`.`customers` (
`customers_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`customers_gender` CHAR(1) NULL,
`customers_firstname` VARCHAR(32) NOT NULL,
`customers_lastname` VARCHAR(32) NULL,
`customers_dob` DATE NULL,
`customers_email_address` VARCHAR(96) NULL,
`customers_default_address_id` INT(5) UNSIGNED NULL,
`customers_telephone` VARCHAR(32) NULL,
`customers_fax` VARCHAR(32) NULL,
`customers_password` VARCHAR(40) NULL,
`customers_newsletter` CHAR(1) NULL,
`customers_info_date_of_last_logon` DATETIME NOT NULL,
`customers_info_number_of_logons` INT(5) UNSIGNED NOT NULL,
`customers_info_date_account_created` TIMESTAMP NOT NULL DEFAULT 0,
`customers_info_date_account_last_modified` TIMESTAMP NOT NULL,
PRIMARY KEY (`customers_id`))
ENGINE = InnoDB;
-表
mydb
。 reviews
DROP TABLE IF EXISTS `mydb`.`reviews` ;
CREATE TABLE IF NOT EXISTS `mydb`.`reviews` (
`reviews_id` INT(5) UNSIGNED NOT NULL,
`products_id` INT(5) UNSIGNED NOT NULL,
`customers_id` INT(5) UNSIGNED NOT NULL,
`customers_name` VARCHAR(64) NULL,
`reviews_rating` INT(1) UNSIGNED NULL,
`date_added` TIMESTAMP NOT NULL DEFAULT 0,
`last_modified` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`reviews_read` INT(5) UNSIGNED NULL,
`reviews_text` TEXT NULL,
PRIMARY KEY (`reviews_id`),
INDEX `fk_reviews_products1_idx` (`products_id` ASC),
INDEX `fk_reviews_customers1_idx` (`customers_id` ASC),
CONSTRAINT `fk_reviews_products1`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_reviews_customers1`
FOREIGN KEY (`customers_id`)
REFERENCES `mydb`.`customers` (`customers_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 specials
DROP TABLE IF EXISTS `mydb`.`specials` ;
CREATE TABLE IF NOT EXISTS `mydb`.`specials` (
`specials_id` INT(5) UNSIGNED NOT NULL,
`products_id` INT(5) UNSIGNED NOT NULL,
`specials_new_products_price` DECIMAL(10,2) UNSIGNED NULL,
`specials_date_added` TIMESTAMP NOT NULL DEFAULT 0,
`specials_last_modified` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`specials_id`),
INDEX `fk_specials_products1_idx` (`products_id` ASC),
CONSTRAINT `fk_specials_products1`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 orders
DROP TABLE IF EXISTS `mydb`.`orders` ;
CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
`orders_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`customers_id` INT(5) UNSIGNED NOT NULL,
`customers_street_address` VARCHAR(64) NOT NULL,
`customers_suburb` VARCHAR(32) NULL,
`customers_city` VARCHAR(32) NOT NULL,
`customers_postcode` VARCHAR(10) NULL,
`customers_state` VARCHAR(32) NULL,
`customers_country` VARCHAR(32) NULL,
`customers_telephone` VARCHAR(32) NULL,
`customers_email_address` VARCHAR(96) NULL,
`delivery_name` VARCHAR(64) NULL,
`delivery_street_address` VARCHAR(64) NULL,
`delivery_suburb` VARCHAR(32) NULL,
`delivery_city` VARCHAR(32) NULL,
`delivery_postcode` VARCHAR(10) NULL,
`delivery_state` VARCHAR(32) NULL,
`delivery_country` VARCHAR(32) NULL,
`payment_method` VARCHAR(12) NULL,
`cc_type` VARCHAR(20) NULL,
`cc_owner` VARCHAR(64) NULL,
`cc_number` VARCHAR(32) NULL,
`cc_expires` VARCHAR(4) NULL,
`last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date_purchased` TIMESTAMP NOT NULL DEFAULT 0,
`shipping_cost` DECIMAL(10,2) UNSIGNED NULL,
`shipping_method` VARCHAR(32) NULL,
`orders_status` VARCHAR(10) NULL,
`orders_date_finished` DATETIME NULL,
`comments` TEXT NULL,
`currency` VARCHAR(3) NULL,
`currency_value` DECIMAL(16,6) NULL,
PRIMARY KEY (`orders_id`),
INDEX `fk_orders_customers1_idx` (`customers_id` ASC),
CONSTRAINT `fk_orders_customers1`
FOREIGN KEY (`customers_id`)
REFERENCES `mydb`.`customers` (`customers_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 ordered_products
DROP TABLE IF EXISTS `mydb`.`ordered_products` ;
CREATE TABLE IF NOT EXISTS `mydb`.`ordered_products` (
`orders_id` INT(5) UNSIGNED NOT NULL,
`products_id` INT(5) UNSIGNED NOT NULL,
`products_size_id` TINYINT UNSIGNED NOT NULL,
`products_color_id` TINYINT UNSIGNED NOT NULL,
`products_price` DECIMAL(10,2) UNSIGNED NOT NULL,
`quantity` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`orders_id`, `products_id`, `products_size_id`, `products_color_id`),
INDEX `fk_orders_has_products_products1_idx` (`products_id` ASC),
INDEX `fk_orders_has_products_orders1_idx` (`orders_id` ASC),
CONSTRAINT `fk_orders_has_products_orders1`
FOREIGN KEY (`orders_id`)
REFERENCES `mydb`.`orders` (`orders_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_has_products_products1`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 ordered_products
DROP TABLE IF EXISTS `mydb`.`ordered_products` ;
CREATE TABLE IF NOT EXISTS `mydb`.`ordered_products` (
`orders_id` INT(5) UNSIGNED NOT NULL,
`products_id` INT(5) UNSIGNED NOT NULL,
`products_size_id` TINYINT UNSIGNED NOT NULL,
`products_color_id` TINYINT UNSIGNED NOT NULL,
`products_price` DECIMAL(10,2) UNSIGNED NOT NULL,
`quantity` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`orders_id`, `products_id`, `products_size_id`, `products_color_id`),
INDEX `fk_orders_has_products_products1_idx` (`products_id` ASC),
INDEX `fk_orders_has_products_orders1_idx` (`orders_id` ASC),
CONSTRAINT `fk_orders_has_products_orders1`
FOREIGN KEY (`orders_id`)
REFERENCES `mydb`.`orders` (`orders_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_has_products_products1`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-表
mydb
。 products_size
DROP TABLE IF EXISTS `mydb`.`products_size` ;
CREATE TABLE IF NOT EXISTS `mydb`.`products_size` (
`products_size_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`products_size_name` VARCHAR(15) NOT NULL,
PRIMARY KEY (`products_size_id`))
ENGINE = InnoDB;
-表
mydb
。 products_color
DROP TABLE IF EXISTS `mydb`.`products_color` ;
CREATE TABLE IF NOT EXISTS `mydb`.`products_color` (
`products_color_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`products_color_name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`products_color_id`))
ENGINE = InnoDB;
-表
mydb
。 products_attributes
DROP TABLE IF EXISTS `mydb`.`products_attributes` ;
CREATE TABLE IF NOT EXISTS `mydb`.`products_attributes` (
`products_id` INT(5) UNSIGNED NOT NULL,
`products_size_id` TINYINT UNSIGNED NOT NULL,
`products_color_id` TINYINT UNSIGNED NOT NULL,
`products_quantity` INT(4) UNSIGNED NOT NULL,
`products_image` VARCHAR(64) NULL,
`products_date_added` TIMESTAMP NOT NULL DEFAULT 0,
`products_last_modified` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`products_date_available` DATETIME NULL,
`products_status` TINYINT UNSIGNED NULL,
PRIMARY KEY (`products_id`, `products_size_id`, `products_color_id`),
INDEX `fk_products_attributes_products_sizes1_idx` (`products_size_id` ASC),
INDEX `fk_products_attributes_products_colors1_idx` (`products_color_id` ASC),
CONSTRAINT `fk_products_attributes_products1`
FOREIGN KEY (`products_id`)
REFERENCES `mydb`.`products` (`products_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_products_attributes_products_sizes1`
FOREIGN KEY (`products_size_id`)
REFERENCES `mydb`.`products_size` (`products_size_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_products_attributes_products_colors1`
FOREIGN KEY (`products_color_id`)
REFERENCES `mydb`.`products_color` (`products_color_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
这是完整的错误:
在服务器中执行SQL脚本
ERROR: Error 1067: Invalid default value for 'last_modified'
SQL Code:
CREATE TABLE IF NOT EXISTS `mydb`.`categories` (
`categories_id` INT(5) UNSIGNED NOT NULL,
`categories_name` VARCHAR(32) NOT NULL,
`categories_image` VARCHAR(64) NULL,
`parent_id` INT(5) UNSIGNED NOT NULL,
`sort_order` INT(3) NULL,
`date_added` TIMESTAMP NOT NULL DEFAULT 0,
`last_modified` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`categories_id`),
INDEX `fk_categories_categories1_idx` (`parent_id` ASC),
CONSTRAINT `fk_categories_categories1`
FOREIGN KEY (`parent_id`)
REFERENCES `mydb`.`categories` (`categories_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
SQL script execution finished: statements: 7 succeeded, 1 failed
以最终形式获取 View 定义。
没什么可取的
最佳答案
这是因为您要设置的SQL_MODE
。TRADITIONAL
和ALLOW_INVALID_DATES
限制TIMESTAMP
类型列不设置为默认值。
通过定义以下任何一项,就可以对TIMESTAMP
类型的列起作用。
DEFAULT 0
DEFAULT CURRENT_TIMESTAMP
或者,只需将
SQL_MODE
设置为ALLOW_INVALID_DATES
,就无需更改脚本。其他:
约束名称必须唯一。表
products_description
定义了Constraint products_id
,但表categories_has_products
中已经使用了相同的名称。维护唯一的约束名称。
请引用:
关于mysql - 错误: Error 1067: Invalid default value for ON UPDATE CURRENT_TIMESTAMP,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23312144/