1

I have created a model with mysql workbench and every time I want to execute the sql file given i get this error

ERROR 1064 (42000) at line 55 in file: 'D:\db.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' CONSTRAINT fk_customer_order_customer1 FOREIGN KEY (customer_id) ' at line 8

ERROR 1064 (42000) at line 76 in file: 'D:\db.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENC' at line 9

ERROR 1064 (42000) at line 98 in file: 'D:\db.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' INDEX fk_ordered_product_product (customer_order_id ASC) VISIBLE, CONST' at line 6

and this is the code mysql workbench generated for me

  -- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema ecommerce_ee
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `ecommerce_ee` ;

-- -----------------------------------------------------
-- Schema ecommerce_ee
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ecommerce_ee` ;
USE `ecommerce_ee` ;

-- -----------------------------------------------------
-- Table `ecommerce_ee`.`customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ecommerce_ee`.`customer` ;

CREATE TABLE IF NOT EXISTS `ecommerce_ee`.`customer` (
  `id` 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(45) NOT NULL,
  `cc_number` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ecommerce_ee`.`category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ecommerce_ee`.`category` ;

CREATE TABLE IF NOT EXISTS `ecommerce_ee`.`category` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ecommerce_ee`.`customer_order`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ecommerce_ee`.`customer_order` ;

CREATE TABLE IF NOT EXISTS `ecommerce_ee`.`customer_order` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `amount` DECIMAL(6,2) NOT NULL,
  `date_created` TIMESTAMP NOT NULL,
  `confirmation_number` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_customer_order_customer` (`customer_id` ASC) VISIBLE,
  CONSTRAINT `fk_customer_order_customer1`
    FOREIGN KEY (`customer_id`)
    REFERENCES `ecommerce_ee`.`customer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ecommerce_ee`.`product`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ecommerce_ee`.`product` ;

CREATE TABLE IF NOT EXISTS `ecommerce_ee`.`product` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `price` DECIMAL(6,2) NOT NULL,
  `description` TINYTEXT NULL,
  `last_update` TIMESTAMP NOT NULL,
  `category_id` TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `name`),
  INDEX `fk_product_category` (`category_id` ASC) VISIBLE,
  CONSTRAINT `fk_product_category`
    FOREIGN KEY (`category_id`)
    REFERENCES `ecommerce_ee`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ecommerce_ee`.`ordered_product`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ecommerce_ee`.`ordered_product` ;

CREATE TABLE IF NOT EXISTS `ecommerce_ee`.`ordered_product` (
  `customer_order_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `quantity` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`customer_order_id`, `product_id`),
  INDEX `fk_ordered_product_customer_order` (`product_id` ASC, `quantity` ASC) VISIBLE,
  INDEX `fk_ordered_product_product` (`customer_order_id` ASC) VISIBLE,
  CONSTRAINT `fk_customer_order_has_product_customer_order1`
    FOREIGN KEY (`customer_order_id`)
    REFERENCES `ecommerce_ee`.`customer_order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_customer_order_has_product_product1`
    FOREIGN KEY (`product_id` , `quantity`)
    REFERENCES `ecommerce_ee`.`product` (`id` , `name`)
    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;
2

1 Answer 1

9

Remove the keyword VISIBLE. (It was just before the 'near' text.)

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks this did work for me the other solutions didn't
VISIBLE is a very new keyword; it is the default. Workbench seems to be too eager to unnecessarily include it. It should not do so because it leads to backward incompatibilities, as you found out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.