Error 1215: Cannot add foreign key constraint

[joserodriguezan]

Hola a todos,

Tengo el "Error 1215:Cannot add foreign key constraint" al crear una tabla con tres foreing keys.

Código MySQL:

CREATE TABLE IF NOT EXISTS historic 
( codJug SMALLINT(3) UNSIGNED NOT NULL PRIMARY KEY,  
  dataini DATE NOT NULL CHECK(dataini>=datafi), 
  datafi DATE, 
  codClub TINYINT(2) UNSIGNED NOT NULL, 
  nomGols SMALLINT(3) UNSIGNED, 
  codEsp TINYINT(2) UNSIGNED default 1 NOT NULL,
  imports SMALLINT(3) UNSIGNED,
  FOREIGN KEY (codJug) REFERENCES jugador (codJug) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY (codClub) REFERENCES club (codClub) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY (codEsp) REFERENCES especialitat (codEsp) ON DELETE SET NULL ON UPDATE CASCADE
) engine=InnoDB;
 

Las entidades a las que hay que relacionar son:

Código MySQL:

CREATE TABLE `jugador` (
  `codJug` smallint(3) unsigned NOT NULL,
  `alcada` double DEFAULT '1.7',
  `pes` double DEFAULT '70',
  PRIMARY KEY (`codJug`),
  CONSTRAINT `jugador_ibfk_1` FOREIGN KEY (`codJug`) REFERENCES `persona` (`codP`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

Código MySQL:

CREATE TABLE `club` (
  `codClub` tinyint(2) unsigned NOT NULL,
  `nomClub` varchar(30) NOT NULL,
  `numSoc` int(11) DEFAULT NULL,
  PRIMARY KEY (`codClub`),
  UNIQUE KEY `nomClub` (`nomClub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

Código MySQL:

CREATE TABLE `especialitat` (
  `codEsp` tinyint(2) unsigned NOT NULL,
  `nom` varchar(30) NOT NULL,
  `descr` varchar(50) DEFAULT 'Sense descripcio',
  PRIMARY KEY (`codEsp`),
  UNIQUE KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

Como veis las restricciones son las mismas en las tablas principles que en la tabla ha crear. ¿A que puede deverse este error?

Muchas gracias,
Jose Luis,