I am trying to send my Database from Mysql Workbench version(10.4.11) to my localhost/phpmyadmin ,(on windows 10 using mysql and xampp) I havw mysql and apache ON ,but I get this error when I try to Forward engineer my DB ,
Any suggestions ,questions or an another easier way to do it would be appreciated Basically what I want to have is these tables connected and be able to run a simple query that will for example create a Driver and require his ID,Name etc.
Executing SQL script in server
ERROR: Error 1064: 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_tblDriver_tblPolicy1`
FOREIGN KEY (`tblPolicy_idPolicy`)
' at line 17
SQL Code:
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblDriver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblDriver` (
`idDriver` INT NOT NULL,
`FirstName` VARCHAR(40) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`Age` INT NULL,
`Street address` VARCHAR(40) NULL,
`AddressPostcode` VARCHAR(45) NULL,
`Phone Number` INT NULL,
`LicenseStatus` VARCHAR(20) NOT NULL,
`DateLicenced` VARCHAR(45) NOT NULL,
`LicenceNumber` INT NOT NULL,
`tblPolicy_idPolicy` INT NOT NULL,
PRIMARY KEY (`idDriver`, `tblPolicy_idPolicy`),
INDEX `fk_tblDriver_tblPolicy1_idx` (`tblPolicy_idPolicy` ASC) VISIBLE,
CONSTRAINT `fk_tblDriver_tblPolicy1`
FOREIGN KEY (`tblPolicy_idPolicy`)
REFERENCES `InsuraceCO`.`tblPolicy` (`idPolicy`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
SQL script execution finished: statements: 6 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
And here is my code
-- 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 InsuraceCO
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema InsuraceCO
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `InsuraceCO` DEFAULT CHARACTER SET utf8 ;
USE `InsuraceCO` ;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblPolicy`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblPolicy` (
`idPolicy` INT NOT NULL,
`PolicyEffectiveDate` VARCHAR(45) NULL,
`PolicyExpirationDate` VARCHAR(45) NULL,
`TotalCost` INT NULL,
`PlayerID` INT NULL,
`LastUpdate` VARCHAR(45) NULL,
`tblPolicycol` VARCHAR(45) NULL,
PRIMARY KEY (`idPolicy`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblDriver`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblDriver` (
`idDriver` INT NOT NULL,
`FirstName` VARCHAR(40) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`Age` INT NULL,
`Street address` VARCHAR(40) NULL,
`AddressPostcode` VARCHAR(45) NULL,
`Phone Number` INT NULL,
`LicenseStatus` VARCHAR(20) NOT NULL,
`DateLicenced` VARCHAR(45) NOT NULL,
`LicenceNumber` INT NOT NULL,
`tblPolicy_idPolicy` INT NOT NULL,
PRIMARY KEY (`idDriver`, `tblPolicy_idPolicy`),
INDEX `fk_tblDriver_tblPolicy1_idx` (`tblPolicy_idPolicy` ASC) VISIBLE,
CONSTRAINT `fk_tblDriver_tblPolicy1`
FOREIGN KEY (`tblPolicy_idPolicy`)
REFERENCES `InsuraceCO`.`tblPolicy` (`idPolicy`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblVehicle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblVehicle` (
`idVehicle` INT NOT NULL,
`VehiclePlateNumber` VARCHAR(45) NULL,
`VehicleBrand` VARCHAR(45) NULL,
`VehicleModel` VARCHAR(45) NULL,
`RegistrationYear` VARCHAR(45) NULL,
`RegistrationNumber` VARCHAR(45) NULL,
`EngineNumber` VARCHAR(45) NULL,
`EngineSize` VARCHAR(45) NULL,
`idPolicy` INT NOT NULL,
`tblPolicy_idPolicy` INT NOT NULL,
PRIMARY KEY (`idVehicle`, `tblPolicy_idPolicy`),
INDEX `fk_tblVehicle_tblPolicy1_idx` (`tblPolicy_idPolicy` ASC) VISIBLE,
CONSTRAINT `fk_tblVehicle_tblPolicy1`
FOREIGN KEY (`tblPolicy_idPolicy`)
REFERENCES `InsuraceCO`.`tblPolicy` (`idPolicy`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblViolationCodes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblViolationCodes` (
`idViolationCodes` INT NOT NULL,
`ViolationDescription` VARCHAR(1000) NULL,
PRIMARY KEY (`idViolationCodes`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblDriver_tblVehicle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblDriver_tblVehicle` (
`tblDriver_intDriverID` INT NOT NULL,
`tblVehicle_intVehicle` INT NOT NULL,
PRIMARY KEY (`tblDriver_intDriverID`, `tblVehicle_intVehicle`),
INDEX `fk_tblDriver_has_tblVehicle_tblVehicle1_idx` (`tblVehicle_intVehicle` ASC) VISIBLE,
INDEX `fk_tblDriver_has_tblVehicle_tblDriver_idx` (`tblDriver_intDriverID` ASC) VISIBLE,
CONSTRAINT `fk_tblDriver_has_tblVehicle_tblDriver`
FOREIGN KEY (`tblDriver_intDriverID`)
REFERENCES `InsuraceCO`.`tblDriver` (`idDriver`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_tblDriver_has_tblVehicle_tblVehicle1`
FOREIGN KEY (`tblVehicle_intVehicle`)
REFERENCES `InsuraceCO`.`tblVehicle` (`idVehicle`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `InsuraceCO`.`tblDriver_tblViolationCodes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InsuraceCO`.`tblDriver_tblViolationCodes` (
`tblDriver_intDriverID` INT NOT NULL,
`tblViolationCodes_idtblViolationCodes` INT NOT NULL,
PRIMARY KEY (`tblDriver_intDriverID`, `tblViolationCodes_idtblViolationCodes`),
INDEX `fk_tblDriver_has_tblViolationCodes_tblViolationCodes1_idx` (`tblViolationCodes_idtblViolationCodes` ASC) VISIBLE,
INDEX `fk_tblDriver_has_tblViolationCodes_tblDriver1_idx` (`tblDriver_intDriverID` ASC) VISIBLE,
CONSTRAINT `fk_tblDriver_has_tblViolationCodes_tblDriver1`
FOREIGN KEY (`tblDriver_intDriverID`)
REFERENCES `InsuraceCO`.`tblDriver` (`idDriver`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_tblDriver_has_tblViolationCodes_tblViolationCodes1`
FOREIGN KEY (`tblViolationCodes_idtblViolationCodes`)
REFERENCES `InsuraceCO`.`tblViolationCodes` (`idViolationCodes`)
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;```
