mysql workbench error [closed]

Question

mysql workbench error [closed]

Navigation

#1 by (2 votes)

1

I'm trying to make a database for an academic project, I'm doing the model in the workbench and then I try to synchronize with phpmyadmin, it happens that when I do the foreign keys, the same error always happens, but I can not figure out what the error is. , is very specific, so I have searched google and found no 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;

Thank you guys!

mysql foreign-key

asked by anonymous 31.12.2018 / 17:23

1 answer

Filter using only month and year in SQLSERVER Create Trigger to change record only when modifying a field

score 2 · Accepted Answer

Good afternoon,

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

You can see the modeling version of the bank in Model > Model Options > MySQL

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