I got a problem for when i try to do a many-to-many relation (in innodb using foreign keys) between my three tables. I have created a "joined"-table for the three table to link foreign keys to. Only one table can create an foreign key the rest gets an 1005 error.

it might be that I'm doing this wrong so please correct me if i go about this the wrong way.

sample code given.
Java Code:
CREATE DATABASE `trotterdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

CREATE TABLE  `trotterdb`.`horse_race_jockey` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `trotterdb`.`horse` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `trotterdb`.`jockey` (
  `c` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `trotterdb`.`race` (
  `b` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
only this foreign key works
Java Code:
DROP TABLE IF EXISTS `trotterdb`.`horse`;
CREATE TABLE  `trotterdb`.`horse` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`),
  CONSTRAINT `FK_horse_1` FOREIGN KEY (`a`) REFERENCES `horse_race_jockey` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

*works fine
any other results in error 1005...even though they are the same (to me).

Java Code:
DROP TABLE IF EXISTS `trotterdb`.`jockey`;
CREATE TABLE  `trotterdb`.`jockey` (
  `c` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c`),
  CONSTRAINT `FK_jockey_1` FOREIGN KEY (`c`) REFERENCES
`horse_race_jockey` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

*gives error 1005
Is there someone who can cast some light on this matter it would be much appreciated