0

I have a problem.

I created a MySQL schema with the MySQL Workbench.

But there is a error message.

A comma or closing bracket was expected. (near "VISIBLE" at position 288)
Unexpected start of statement. (near "`ShopID`" at position 330)
Unrecognised statement type. (near "ASC" at position 339)

As I said I created the scheme via the MySQL workbench. But what is wrong ?

Note: PK = Primary Key; FK = Foreign Key. Fact table:

  • Contaminants (TimeID (PK/FK), ShopID (PK/FK), FoodID (PK/FK), substance, quantityPerOunce)

Dimension tables:

  • Time (TimeID (PK), dayNr, dayName, weekNr, monthNr, year)
  • Food (FoodID (PK), foodName, brand, foodType)
  • Place (ShopID (PK), name, street, region, country)
CREATE TABLE IF NOT EXISTS `mydb`.`Contaminants` (
  `TimeID` INT NOT NULL,
  `ShopID` INT NOT NULL,
  `FoodID` INT NOT NULL,
  `substance` VARCHAR(45) NULL,
  `quantityPerOunce` INT NULL,
  PRIMARY KEY (`TimeID`, `ShopID`, `FoodID`),
  UNIQUE INDEX `TimeID_UNIQUE` (`TimeID` ASC) VISIBLE,
  UNIQUE INDEX `ShopID_UNIQUE` (`ShopID` ASC) VISIBLE,
  UNIQUE INDEX `FoodID_UNIQUE` (`FoodID` ASC) VISIBLE,
  CONSTRAINT `TimeID`
    FOREIGN KEY (`TimeID`)
    REFERENCES `mydb`.`Time` (`TimeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ShopID`
    FOREIGN KEY (`ShopID`)
    REFERENCES `mydb`.`Shop` (`ShopID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FoodID`
    FOREIGN KEY (`FoodID`)
    REFERENCES `mydb`.`Food` (`FoodID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `mydb`.`Food` (
  `FoodID` INT NOT NULL,
  `foodName` VARCHAR(45) NULL,
  `brand` VARCHAR(45) NULL,
  `foodType` VARCHAR(45) NULL,
  PRIMARY KEY (`FoodID`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `mydb`.`Shop` (
  `ShopID` INT NOT NULL,
  `INT` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `region` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  PRIMARY KEY (`ShopID`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `mydb`.`Time` (
  `TimeID` INT NOT NULL,
  `dayNr` INT NULL,
  `dayName` VARCHAR(45) NULL,
  `weekNr` INT NULL,
  `monthNr` VARCHAR(45) NULL,
  `year` INT NULL,
  PRIMARY KEY (`TimeID`))
ENGINE = InnoDB
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Test
  • 571
  • 13
  • 32
  • Can you query `SELECT VERSION()` and report what it says? – Bill Karwin Feb 04 '22 at 15:38
  • Sure! 10.4.22-MariaDB – Test Feb 04 '22 at 15:40
  • 1
    MariaDB doesn't support the `VISIBLE` feature of MySQL. See https://stackoverflow.com/questions/50393245/mysql-error-1064-syntax-but-everything-seems-fine/50393991#50393991 – Bill Karwin Feb 04 '22 at 15:41
  • 1
    In general, you should not think of MariaDB as compatible with MySQL. MariaDB started with code licensed from MySQL in 2010, but it forked, and both products have been evolving since then. Features implemented in one are not necessarily going to work the same in the other. – Bill Karwin Feb 04 '22 at 15:42

0 Answers0