MySQL错误：写入或更新时键重复

Question

我一直在使用MySQL来设计一个数据库，当将其编译为SQL代码并执行时，会产生两个错误。一个错误提示：变量“唯一检查”不能设置为“NULL”的值，另一个错误提示：写入和更新时出现重复键。我有一种感觉是我的外键导致了这个问题，但仍然无法解决这个问题。我在下方附上了查询代码。

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;

- GTucker

Answer 1

您已经在表mydb.issue上有一个名为FK_USERID的约束。请给mydb.Forum上的约束起一个其他的名称，例如：

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;

- Olantobi

Answer 2

我在恢复数据库时遇到了这个错误，我找到了以下细节：

当您的表/数据库名称在两侧使用小写/大写字母时，与同一表/数据库中使用的大小写不同的名称相同时，就会出现此问题（需要在两侧保持相同的名称，因为它是区分大小写的）。

- user10589371