mysql Workbench error

Asked

Viewed 929 times

2

I’m trying to make a database for an academic project, I’m doing the model on the Workbench and then I try to synchronize with phpmyadmin, it turns out that when making the Foreign Keys the same mistake always happens, but I can’t figure out what mistake this is, is very specific, hence have searched on google and have not found solution.

Error:

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 'VISIBLE,   CONSTRAINT `fk_users_bookRegistration1`
    FOREIGN KEY (`bookRegistr' at line 11 SQL Code:
        CREATE TABLE IF NOT EXISTS `libraryRat`.`users` (
          `userID` INT(11) NOT NULL AUTO_INCREMENT,
          `name` VARCHAR(45) NULL DEFAULT NULL,
          `email` VARCHAR(45) NULL DEFAULT NULL,
          `password` VARCHAR(80) NULL DEFAULT NULL,
          `birthdate` DATE NULL DEFAULT NULL,
          `booksReaden` VARCHAR(45) NULL DEFAULT NULL,
          `wishList` VARCHAR(45) NULL DEFAULT NULL,
          `bookRegistration_userID` INT(11) NOT NULL,
          PRIMARY KEY (`userID`),
          INDEX `fk_users_bookRegistration1_idx` (`bookRegistration_userID` ASC) VISIBLE,
          CONSTRAINT `fk_users_bookRegistration1`
            FOREIGN KEY (`bookRegistration_userID`)
            REFERENCES `libraryRat`.`bookRegistration` (`userID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

SQL script execution finished: statements: 3 succeeded, 1 failed
Fetching back view definitions in final form. Nothing to fetch

Mysql code:

   -- MySQL Workbench Synchronization
-- Generated: 2018-12-31 01:55
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: nelson

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

CREATE TABLE IF NOT EXISTS `libraryRat`.`users` (
  `userID` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `email` VARCHAR(45) NULL DEFAULT NULL,
  `password` VARCHAR(80) NULL DEFAULT NULL,
  `birthdate` DATE NULL DEFAULT NULL,
  `booksReaden` VARCHAR(45) NULL DEFAULT NULL,
  `wishList` VARCHAR(45) NULL DEFAULT NULL,
  `bookRegistration_userID` INT(11) NOT NULL,
  PRIMARY KEY (`userID`),
  INDEX `fk_users_bookRegistration_idx` (`bookRegistration_userID` ASC) VISIBLE,
  CONSTRAINT `fk_users_bookRegistration`
    FOREIGN KEY (`bookRegistration_userID`)
    REFERENCES `libraryRat`.`bookRegistration` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`books` (
  `bookID` INT(11) NOT NULL,
  `name` VARCHAR(80) NULL DEFAULT NULL,
  `description` VARCHAR(200) NULL DEFAULT NULL,
  `isbn` INT(11) NULL DEFAULT NULL,
  `cover` VARCHAR(150) NULL DEFAULT NULL,
  `rating` DECIMAL(50) NULL DEFAULT NULL,
  `genres` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`bookID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`author` (
  `authorID` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `birthdate` DATE NULL DEFAULT NULL,
  `about` VARCHAR(500) NULL DEFAULT NULL,
  PRIMARY KEY (`authorID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`admin` (
  `idAdmin` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `password` VARCHAR(45) NULL DEFAULT NULL,
  `approvals` VARCHAR(45) NULL DEFAULT NULL,
  `bookApprovals_adminID` INT(11) NOT NULL,
  PRIMARY KEY (`idAdmin`),
  INDEX `fk_admin_bookApprovals1_idx` (`bookApprovals_adminID` ASC) VISIBLE,
  CONSTRAINT `fk_admin_bookApprovals1`
    FOREIGN KEY (`bookApprovals_adminID`)
    REFERENCES `libraryRat`.`bookApprovals` (`adminID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`ratings` (
  `ratingID` DECIMAL(50) NOT NULL,
  `bookID` INT(11) NOT NULL,
  `date` DATE NULL DEFAULT NULL,
  `rating` INT(11) NULL DEFAULT NULL,
  `comments` VARCHAR(150) NULL DEFAULT NULL,
  PRIMARY KEY (`ratingID`, `bookID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`bookRegistration` (
  `userID` INT(11) NOT NULL AUTO_INCREMENT,
  `date` VARCHAR(45) NULL DEFAULT NULL,
  `bookID` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `authorID` VARCHAR(45) NULL DEFAULT NULL,
  `genres` VARCHAR(45) NULL DEFAULT NULL,
  `state` VARCHAR(45) NULL DEFAULT NULL,
  `books_bookID` INT(11) NOT NULL,
  `isbn` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`userID`, `bookID`),
  INDEX `fk_bookRegistration_books1_idx` (`books_bookID` ASC) VISIBLE,
  CONSTRAINT `fk_bookRegistration_books1`
    FOREIGN KEY (`books_bookID`)
    REFERENCES `libraryRat`.`books` (`bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`bookApprovals` (
  `adminID` INT(11) NOT NULL AUTO_INCREMENT,
  `date` VARCHAR(45) NULL DEFAULT NULL,
  `bookID` VARCHAR(45) NULL DEFAULT NULL,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `authorID` VARCHAR(45) NULL DEFAULT NULL,
  `genres` VARCHAR(45) NULL DEFAULT NULL,
  `userID` VARCHAR(45) NULL DEFAULT NULL,
  `state` VARCHAR(45) NULL DEFAULT NULL,
  `bookRegistration_userID` INT(11) NOT NULL,
  PRIMARY KEY (`adminID`),
  INDEX `fk_bookApprovals_bookRegistration1_idx` (`bookRegistration_userID` ASC) VISIBLE,
  CONSTRAINT `fk_bookApprovals_bookRegistration1`
    FOREIGN KEY (`bookRegistration_userID`)
    REFERENCES `libraryRat`.`bookRegistration` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`books_has_ratings` (
  `books_bookID` INT(11) NOT NULL,
  `ratings_ratingID` DECIMAL(50) NOT NULL,
  `ratings_bookID` INT(11) NOT NULL,
  PRIMARY KEY (`books_bookID`, `ratings_ratingID`, `ratings_bookID`),
  INDEX `fk_books_has_ratings_ratings1_idx` (`ratings_ratingID` ASC, `ratings_bookID` ASC) VISIBLE,
  INDEX `fk_books_has_ratings_books_idx` (`books_bookID` ASC) VISIBLE,
  CONSTRAINT `fk_books_has_ratings_books`
    FOREIGN KEY (`books_bookID`)
    REFERENCES `libraryRat`.`books` (`bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_has_ratings_ratings1`
    FOREIGN KEY (`ratings_ratingID` , `ratings_bookID`)
    REFERENCES `libraryRat`.`ratings` (`ratingID` , `bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`users_has_ratings` (
  `users_userID` INT(11) NOT NULL,
  `ratings_ratingID` DECIMAL(50) NOT NULL,
  `ratings_bookID` INT(11) NOT NULL,
  PRIMARY KEY (`users_userID`, `ratings_ratingID`, `ratings_bookID`),
  INDEX `fk_users_has_ratings_ratings1_idx` (`ratings_ratingID` ASC, `ratings_bookID` ASC) VISIBLE,
  INDEX `fk_users_has_ratings_users1_idx` (`users_userID` ASC) VISIBLE,
  CONSTRAINT `fk_users_has_ratings_users1`
    FOREIGN KEY (`users_userID`)
    REFERENCES `libraryRat`.`users` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_ratings_ratings1`
    FOREIGN KEY (`ratings_ratingID` , `ratings_bookID`)
    REFERENCES `libraryRat`.`ratings` (`ratingID` , `bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `libraryRat`.`author_has_books` (
  `author_authorID` INT(11) NOT NULL,
  `books_bookID` INT(11) NOT NULL,
  PRIMARY KEY (`author_authorID`, `books_bookID`),
  INDEX `fk_author_has_books_books1_idx` (`books_bookID` ASC) VISIBLE,
  INDEX `fk_author_has_books_author1_idx` (`author_authorID` ASC) VISIBLE,
  CONSTRAINT `fk_author_has_books_author1`
    FOREIGN KEY (`author_authorID`)
    REFERENCES `libraryRat`.`author` (`authorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_author_has_books_books1`
    FOREIGN KEY (`books_bookID`)
    REFERENCES `libraryRat`.`books` (`bookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Personal thank you!

mysql foreign-key

  • As already answered, this VISIBLE is not part of the DB syntax used. As the error message says, you have a SYNTAX error and suggests looking at the manual of the version of Mariadb used. Important when so, post the DB version in the question and preferably highlight the relevant part of the error message.

1 answer

2


Good afternoon,

The problem is that your script is in version 8 and your database is in a lower version than the script
see at this link

You can see the database modeling version in Model > Model Options > Mysql

You may be removing the "VISIBLE", by default it comes already enabled.

Browser other questions tagged mysql foreign-key

You are not signed in. Login or sign up in order to post.