CREATE  TABLE IF NOT EXISTS `mydb`.`MATCHES` ( 
  `idMatch` INT NOT NULL , 
  `idChampionship` INT NOT NULL , 
  `idWinningTeam` INT NOT NULL ,
  `idWLoosingTeam` INT NOT NULL , 
  `date` TIMESTAMP NULL DEFAULT NULL , 
  `goalsWinningTeam` INT NULL DEFAULT -1 ,
  `goalsLoosingTeam` INT NULL DEFAULT -1 , 
  `played` CHAR NULL DEFAULT 'Y' , 
  PRIMARY KEY (`idMatch`) , 
  INDEX `ID_TEAM_X_CHAMP` (`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;

开发者_JAVA百科

I'm trying to make a matches table and I'm not sure how to set winning and losing team,

both by idTeam (can I use same foreign key for both?) I have a Team table a Championship

table and teams_per_champsionship table (for indexing).

schema available

Thanks you very much

---

An example schema:

Team Table
----------
TeamID pk
rest of the team information

Championship Table
------------------
ChampID  pk
rest of the championship info

Match Table
-----------
MatchID pk
winningTeamID fk
losingTeamID fk
rest of the match info

ChampMatchTable
---------------
ChampId fk (dups)
MachID fk

OR

You could put the ChampID in the match table and not have the ChampMatchTable.

original answer below

Yes you can not use the same key for both. They both are keys to the same table but they are different (since each team is different.)

Not clear what the idChamionship key points to. Is this a table that describes an event which has matches?