sql - 错误代码1005在3个表上的错误号121我在做什么错？

码：

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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

-表mydb。SEX

CREATE  TABLE IF NOT EXISTS `mydb`.`SEX` (
  `idSex` INT NOT NULL AUTO_INCREMENT ,
  `descrip` VARCHAR(15) NOT NULL ,
  PRIMARY KEY (`idSex`) )
ENGINE = InnoDB;

-表mydb。TEAMS

CREATE  TABLE IF NOT EXISTS `mydb`.`TEAMS` (
  `idTeam` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  `idSex` INT NOT NULL ,
 PRIMARY KEY (`idTeam`) ,
  INDEX `ID_SEX` (`idSex` ASC) ,
  CONSTRAINT `ID_SEX`
    FOREIGN KEY (`idSex` )
    REFERENCES `mydb`.`SEX` (`idSex` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。CHAMPIONSHIPS

CREATE  TABLE IF NOT EXISTS `mydb`.`CHAMPIONSHIPS` (
  `idChampionship` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NOT NULL ,
  `totalPlayingDays` INT NULL ,
  `startDate` DATE NULL ,
  `EndDate` DATE NULL ,
  `active` CHAR NULL ,
  `idSex` INT NOT NULL ,
  PRIMARY KEY (`idChampionship`) ,
  INDEX `ID_SEX` (`idSex` ASC) ,
  CONSTRAINT `ID_SEX`
    FOREIGN KEY (`idSex` )
    REFERENCES `mydb`.`SEX` (`idSex` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。TEAMS_PER_CHAMPIONSHIP

CREATE  TABLE IF NOT EXISTS `mydb`.`TEAMS_PER_CHAMPIONSHIP` (
  `idTeam` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  PRIMARY KEY (`idTeam`, `idChampionship`) ,
  INDEX `ID_TEAM` (`idTeam` ASC) ,
  INDEX `ID_CHAMP` (`idChampionship` ASC) ,
  CONSTRAINT `ID_TEAM`
    FOREIGN KEY (`idTeam` )
    REFERENCES `mydb`.`TEAMS` (`idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_CHAMP`
    FOREIGN KEY (`idChampionship` )
    REFERENCES `mydb`.`CHAMPIONSHIPS` (`idChampionship` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。MATCHES

CREATE  TABLE IF NOT EXISTS `mydb`.`MATCHES` (
  `idMatch` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  `idTeam` INT NOT NULL ,
  `date` TIMESTAMP NULL DEFAULT NULL ,
  `gols` INT NULL DEFAULT -1 ,
  `isLocal` CHAR NULL DEFAULT 'N' ,
  PRIMARY KEY (`idMatch`, `idChampionship`, `idTeam`) ,
  INDEX `ID_TEAM_X_CHAMP` (`idChampionship` ASC, `idMatch` ASC) ,
  CONSTRAINT `ID_TEAM_X_CHAMP`
    FOREIGN KEY (`idChampionship` , `idMatch` )
    REFERENCES `mydb`.`TEAMS_PER_CHAMPIONSHIP` (`idChampionship` , `idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。PLAYERS

CREATE  TABLE IF NOT EXISTS `mydb`.`PLAYERS` (
  `idPlayer` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `lastname` VARCHAR(45) NULL ,
  `performance` VARCHAR(45) NULL ,
  `idTeam` INT NOT NULL ,
  `idSex` INT NOT NULL ,
  PRIMARY KEY (`idPLayer`) ,
  INDEX `ID_TEAM` (`idTeam` ASC) ,
  INDEX `ID_SEX` (`idSex` ASC) ,
  CONSTRAINT `ID_TEAM`
    FOREIGN KEY (`idTeam` )
    REFERENCES `mydb`.`TEAMS` (`idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_SEX`
    FOREIGN KEY (`idSex` )
    REFERENCES `mydb`.`SEX` (`idSex` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。MASTERS

CREATE  TABLE IF NOT EXISTS `mydb`.`MASTERS` (
  `idPlayer` INT NOT NULL ,
  `idMatch` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  `idteam` INT NOT NULL ,
  `gols` INT NOT NULL DEFAULT -1 ,
  PRIMARY KEY (`idPlayer`, `idMatch`, `idChampionship`, `idTeam`) ,
  INDEX `ID_MATCH` (`idMatch` ASC, `idChampionship` ASC, `idTeam` ASC) ,
  INDEX `ID_PLAY` (`idPLayer` ASC) ,
  CONSTRAINT `ID_MATCH`
    FOREIGN KEY (`idMatch` , `idChampionship` , `idTeam` )
    REFERENCES `mydb`.`MATCHES` (`idMatch` , `idChampionship` , `idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_PLAY`
    FOREIGN KEY (`idPlayer` )
    REFERENCES `mydb`.`PLAYERS` (`idPlayer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-表mydb。SANCTIONS

CREATE  TABLE IF NOT EXISTS `mydb`.`SANCTIONS` (
  `idSanction` INT NOT NULL ,
  `descrip` VARCHAR(100) NOT NULL DEFAULT 'A DEFINIR' ,
  PRIMARY KEY (`idSanction`) )
ENGINE = InnoDB;

-表mydb。SANCTIONS_PLAYERS

CREATE  TABLE IF NOT EXISTS `mydb`.`SANCIONS_PLAYERS` (
  `idPlayer` INT NOT NULL ,
  `idMatch` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  `idTeam` INT NOT NULL ,
  `idSanction` INT NOT NULL ,
  PRIMARY KEY (`idPlayer`, `idMatch`, `idChampionship`, `idTeam`) ,
  INDEX `ID_PLAY` (`idPlayer` ASC) ,
  INDEX `ID_MATCH` (`idMatch` ASC, `idChampionship` ASC, `idTeam` ASC) ,
  INDEX `ID_SANC` (`idSanction` ASC) ,
  CONSTRAINT `ID_PLAY`
    FOREIGN KEY (`idPlayer` )
    REFERENCES `mydb`.`PLAYERS` (`idPlayer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_MATCH`
    FOREIGN KEY (`idMatch` , `idChampionship` , `idTeam` )
    REFERENCES `mydb`.`MATCHES` (`idMatch` , `idChampionship` , `idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_SANC`
    FOREIGN KEY (`idSanction` )
    REFERENCES `mydb`.`SANCTIONS` (`idSanction` )
    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;

最佳答案

我注意到了这一点：

CREATE  TABLE IF NOT EXISTS `mydb`.`MATCHES` (
  `idMatch` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  `idTeam` INT NOT NULL ,
  `date` TIMESTAMP NULL DEFAULT NULL ,
  `gols` INT NULL DEFAULT -1 ,
  `isLocal` CHAR NULL DEFAULT 'N' ,
  PRIMARY KEY (`idMatch`, `idChampionship`, `idTeam`) ,
  INDEX `ID_TEAM_X_CHAMP` (`idChampionship` ASC, `idMatch` ASC) ,
  CONSTRAINT `ID_TEAM_X_CHAMP`
    FOREIGN KEY (`idChampionship` , `idMatch` )
    REFERENCES `mydb`.`TEAMS_PER_CHAMPIONSHIP` (`idChampionship` , `idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

    FOREIGN KEY (`idChampionship` , `idMatch` )
    REFERENCES `mydb`.`TEAMS_PER_CHAMPIONSHIP` (`idChampionship` , `idTeam` )

我认为您的密钥和引用不匹配。这可能无法解决您的整个问题，但仍然需要解决:)。让我知道您的错误是否随着该更新而改变。

编辑：我发现了其他东西。

如MySQL Reference上所列

InnoDB要求在外键和引用键上建立索引，以便外键检查可以快速进行，而无需进行表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。如果这样的索引不存在，则会在引用表上自动创建。 （这与某些旧版本相反，在旧版本中，必须显式创建索引，否则将无法创建外键约束。）如前所述，如果使用index_name，则使用它。

CREATE  TABLE IF NOT EXISTS `mydb`.`TEAMS_PER_CHAMPIONSHIP` (
  `idTeam` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  PRIMARY KEY (`idTeam`, `idChampionship`) ,
  INDEX `ID_TEAM` (`idTeam` ASC) ,
  INDEX `ID_CHAMP` (`idChampionship` ASC) ,
  CONSTRAINT `ID_TEAM`
    FOREIGN KEY (`idTeam` )
    REFERENCES `mydb`.`TEAMS` (`idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ID_CHAMP`
    FOREIGN KEY (`idChampionship` )
    REFERENCES `mydb`.`CHAMPIONSHIPS` (`idChampionship` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`MATCHES` (
  `idMatch` INT NOT NULL ,
  `idChampionship` INT NOT NULL ,
  `idTeam` INT NOT NULL ,
  `date` TIMESTAMP NULL DEFAULT NULL ,
  `gols` INT NULL DEFAULT -1 ,
  `isLocal` CHAR NULL DEFAULT 'N' ,
  PRIMARY KEY (`idMatch`, `idChampionship`, `idTeam`) ,
  INDEX `ID_TEAM_X_CHAMP` (`idChampionship` ASC, `idMatch` ASC) ,
  CONSTRAINT `ID_TEAM_X_CHAMP`
    FOREIGN KEY (`idChampionship` , `idMatch` )
    REFERENCES `mydb`.`TEAMS_PER_CHAMPIONSHIP` (`idChampionship` , `idTeam` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

根据该参考文本，参考键必须与参考表上的顺序相同。您将它们的顺序与主键的顺序不同。