MySQL error: Duplicate key on write or update

Question

I have been using MySQL to design a database, when compiled into SQL code and executed it produces two errors. One that states Variable 'unique checks' can't be set to the value of 'NULL' and another that produces message "Duplicate key on write and update" I have a feeling that it is my foreign keys that are causing this issue but still cannot fix the problem. I have attached the query code below.

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='TRADITIONAL,ALLOW_INVALID_DATES';

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User` ;

CREATE TABLE IF NOT EXISTS `mydb`.`User` (
  `idUser` INT NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `username` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `company_pin` VARCHAR(45) NULL,
  `student_pin` VARCHAR(45) NULL,
  `isAdmin` TINYINT(1) NULL DEFAULT 0,
  `isCandidate` TINYINT(1) NULL,
  `isVoter` TINYINT(1) NULL,
  `votes_left` INT NULL,
  `votes_achieved` INT NULL,
  PRIMARY KEY (`idUser`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Issue`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Issue` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Issue` (
  `idIssue` INT NOT NULL,
  `issueTitle` VARCHAR(45) NULL,
  `issueDescription` VARCHAR(255) NULL,
  `userID` INT NULL,
  `isResolved` TINYINT(1) NULL DEFAULT 0,
  `upVotes` INT NULL,
  PRIMARY KEY (`idIssue`),
  INDEX `FK_USERID_idx` (`userID` ASC),
  CONSTRAINT `FK_USERID`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Forum`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Forum` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Forum` (
  `idForum` INT NOT NULL,
  `userID` INT NULL,
  `postTitle` VARCHAR(45) NULL,
  `postText` VARCHAR(255) NULL,
  `upVotes` INT NULL,
  PRIMARY KEY (`idForum`),
  INDEX `FK_USERID_idx` (`userID` ASC),
  CONSTRAINT `FK_USERID`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Vote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Vote` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Vote` (
  `idVote` INT NOT NULL,
  `voteFor` ENUM('TOPIC', 'USER', 'ISSUE') NULL,
  `topicID` INT NULL,
  `userID` INT NULL,
  `issueID` INT NULL,
  PRIMARY KEY (`idVote`),
  INDEX `FK_USER_VOTED_FOR_idx` (`userID` ASC),
  INDEX `FK_ISSUE_VOTED_FOR_idx` (`issueID` ASC),
  INDEX `FK_FORUM_VOTED_FOR_idx` (`topicID` ASC),
  CONSTRAINT `FK_USER_VOTED_FOR`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_ISSUE_VOTED_FOR`
    FOREIGN KEY (`issueID`)
    REFERENCES `mydb`.`Issue` (`idIssue`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_FORUM_VOTED_FOR`
    FOREIGN KEY (`topicID`)
    REFERENCES `mydb`.`Forum` (`idForum`)
    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;

Solution 1

You already have a Constraint with the name FK_USERID on table mydb.issue. Give the constraint on mydb.Forum another name. e.g.

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='TRADITIONAL,ALLOW_INVALID_DATES';

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User` ;

CREATE TABLE IF NOT EXISTS `mydb`.`User` (
  `idUser` INT NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `username` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `company_pin` VARCHAR(45) NULL,
  `student_pin` VARCHAR(45) NULL,
  `isAdmin` TINYINT(1) NULL DEFAULT 0,
  `isCandidate` TINYINT(1) NULL,
  `isVoter` TINYINT(1) NULL,
  `votes_left` INT NULL,
  `votes_achieved` INT NULL,
  PRIMARY KEY (`idUser`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Issue`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Issue` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Issue` (
  `idIssue` INT NOT NULL,
  `issueTitle` VARCHAR(45) NULL,
  `issueDescription` VARCHAR(255) NULL,
  `userID` INT NULL,
  `isResolved` TINYINT(1) NULL DEFAULT 0,
  `upVotes` INT NULL,
  PRIMARY KEY (`idIssue`),
  INDEX `FK_USERID_idx` (`userID` ASC),
  CONSTRAINT `FK_USERID`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Forum`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Forum` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Forum` (
  `idForum` INT NOT NULL,
  `userID` INT NULL,
  `postTitle` VARCHAR(45) NULL,
  `postText` VARCHAR(255) NULL,
  `upVotes` INT NULL,
  PRIMARY KEY (`idForum`),
  INDEX `FK_USERID_idx` (`userID` ASC),
  CONSTRAINT `FK_Forum_USERID`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Vote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Vote` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Vote` (
  `idVote` INT NOT NULL,
  `voteFor` ENUM('TOPIC', 'USER', 'ISSUE') NULL,
  `topicID` INT NULL,
  `userID` INT NULL,
  `issueID` INT NULL,
  PRIMARY KEY (`idVote`),
  INDEX `FK_USER_VOTED_FOR_idx` (`userID` ASC),
  INDEX `FK_ISSUE_VOTED_FOR_idx` (`issueID` ASC),
  INDEX `FK_FORUM_VOTED_FOR_idx` (`topicID` ASC),
  CONSTRAINT `FK_USER_VOTED_FOR`
    FOREIGN KEY (`userID`)
    REFERENCES `mydb`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_ISSUE_VOTED_FOR`
    FOREIGN KEY (`issueID`)
    REFERENCES `mydb`.`Issue` (`idIssue`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_FORUM_VOTED_FOR`
    FOREIGN KEY (`topicID`)
    REFERENCES `mydb`.`Forum` (`idForum`)
    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;

Solution 2

I got this error at a time of restoring of database & I found below details :

This issue comes into picture when your table/database names are not same like small/capital letters used in same table/database name on both side (needs to maintain same name on both side as it's case_sensitive).