views:

47

answers:

1

code:

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 ;

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

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

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

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

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

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

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

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

-- Table 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;
A: 

I noticed this:


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` )

I think you have your key and references mismatched. This may not solve your entire problem, but it is still something to be fixed :). Let me know if your errors change with that update.

Edit: I found something else.

As listed on MySQL Reference

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

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;

Per that reference text, the referenced keys need to be in the same order as on the referenced tabled. You have them in a different order than your primary key.

Banana
I just tried out changing mismatched references but nothing changed still the same problem, Thanks for looking at it!
Ignacio
I just updated my solution with another thing I have found. I don't know if you are still trying to get this working, but might help.
Banana
It workedddd thank you very muchhhhh
Ignacio