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';



DROP SCHEMA IF EXISTS `cartbean` ;

CREATE SCHEMA IF NOT EXISTS `cartbean` ;

USE `cartbean` ;



-- -----------------------------------------------------

-- Table `cartbean`.`customer`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `cartbean`.`customer` ;



CREATE  TABLE IF NOT EXISTS `cartbean`.`customer` (

  `idcustomer` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(45) NOT NULL ,

  `email` VARCHAR(45) NOT NULL ,

  `phone` VARCHAR(45) NOT NULL ,

  `address` VARCHAR(45) NOT NULL ,

  `city_region` VARCHAR(2) NOT NULL ,

  `cc_number` VARCHAR(19) NOT NULL ,

  PRIMARY KEY (`idcustomer`) )

ENGINE = InnoDB

COMMENT = 'maintains customer details' ;





-- -----------------------------------------------------

-- Table `cartbean`.`customer_order`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `cartbean`.`customer_order` ;



CREATE  TABLE IF NOT EXISTS `cartbean`.`customer_order` (

  `idcustomer_order` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `amount` DECIMAL(6,2) NOT NULL ,

  `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,

  `confirmation_number` INT UNSIGNED NOT NULL ,

  `customer_idcustomer` INT UNSIGNED NOT NULL ,

  PRIMARY KEY (`idcustomer_order`) ,

  INDEX `fk_customer_order_customer` (`customer_idcustomer` ASC) ,

  CONSTRAINT `fk_customer_order_customer`

    FOREIGN KEY (`customer_idcustomer` )

    REFERENCES `cartbean`.`customer` (`idcustomer` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB, 

COMMENT = 'maintains customer order details\n' ;





-- -----------------------------------------------------

-- Table `cartbean`.`category`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `cartbean`.`category` ;



CREATE  TABLE IF NOT EXISTS `cartbean`.`category` (

  `idcategory` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(45) NOT NULL ,

  PRIMARY KEY (`idcategory`) )

ENGINE = InnoDB, 

COMMENT = 'maintains category details' ;





-- -----------------------------------------------------

-- Table `cartbean`.`product`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `cartbean`.`product` ;



CREATE  TABLE IF NOT EXISTS `cartbean`.`product` (

  `idproduct` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(45) NOT NULL ,

  `price` DECIMAL(5,2) NOT NULL ,

  `description` TINYTEXT NULL ,

  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  `category_idcategory` TINYINT UNSIGNED NOT NULL ,

  PRIMARY KEY (`idproduct`) ,

  INDEX `fk_product_category` (`category_idcategory` ASC) ,

  CONSTRAINT `fk_product_category`

    FOREIGN KEY (`category_idcategory` )

    REFERENCES `cartbean`.`category` (`idcategory` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB, 

COMMENT = 'maintains product details' ;





-- -----------------------------------------------------

-- Table `cartbean`.`ordered_product`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `cartbean`.`ordered_product` ;



CREATE  TABLE IF NOT EXISTS `cartbean`.`ordered_product` (

  `customer_order_idcustomer_order` INT UNSIGNED NOT NULL ,

  `product_idproduct` INT UNSIGNED NOT NULL ,

  `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 1 ,

  PRIMARY KEY (`customer_order_idcustomer_order`, `product_idproduct`) ,

  INDEX `fk_ordered_product_product` (`product_idproduct` ASC) ,

  INDEX `fk_ordered_product_customer_order` () ,

  CONSTRAINT `fk_ordered_product_customer_order`

    FOREIGN KEY ()

    REFERENCES `cartbean`.`customer_order` ()

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_ordered_product_product`

    FOREIGN KEY (`product_idproduct` )

    REFERENCES `cartbean`.`product` (`idproduct` )

    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;

Executing SQL script in server

ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ,

  CONSTRAINT `fk_ordered_product_customer_order`

    FOREIGN KEY ()

    REFE' at line 7





CREATE  TABLE IF NOT EXISTS `cartbean`.`ordered_product` (

  `customer_order_idcustomer_order` INT UNSIGNED NOT NULL ,

  `product_idproduct` INT UNSIGNED NOT NULL ,

  `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 1 ,

  PRIMARY KEY (`customer_order_idcustomer_order`, `product_idproduct`) ,

  INDEX `fk_ordered_product_product` (`product_idproduct` ASC) ,

  INDEX `fk_ordered_product_customer_order` () ,

  CONSTRAINT `fk_ordered_product_customer_order`

    FOREIGN KEY ()

    REFERENCES `cartbean`.`customer_order` ()

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_ordered_product_product`

    FOREIGN KEY (`product_idproduct` )

    REFERENCES `cartbean`.`product` (`idproduct` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB



SQL script execution finished: statements: 15 succeeded, 1 failed