错误代码1005错误号码121在3桌上我做错了什么?
SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@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 [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_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`)
我觉得你有你的钥匙和引用不匹配。这可能无法解决你的整个问题,但它仍然是一个需要修正的问题:)。让我知道如果您的错误随着更新而改变。
编辑:我发现了别的东西。
上市的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;
根据该引用文本,引用的键需要与引用的表中的顺序相同。您拥有的顺序与您的主键不同。
我刚刚尝试更改不匹配的引用,但没有任何更改仍然是相同的问题,感谢您看它! – 2010-09-21 13:11:16
我刚刚用我发现的另一件事更新了我的解决方案。我不知道你是否仍然试图做到这一点,但可能会有所帮助。 – Banana 2010-09-21 23:16:49
非常感谢你的工作 – 2010-09-25 19:06:55
案例错误正在修复,我已经多次掺杂并创建了模式,但仍然无法正常工作。 – 2010-09-21 04:06:05