mysql - 错误代码 : 1215. 无法添加外键约束 MySQL

Question

有谁知道我为什么会出错

Error Code: 1215. Cannot add foreign key constraint

当我尝试

ALTER TABLE hermanos ADD CONSTRAINT fk_hno_provincia FOREIGN KEY (provincia) REFERENCES p_provincias (id)

在这些表上:

CREATE TABLE IF NOT EXISTS `hermanos` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) NOT NULL,
`apellidos` varchar(255) NOT NULL,
`direccion` varchar(255) NOT NULL,
`codigoPostal` int(11) NOT NULL,
`provincia` int(11) NOT NULL,
`numeroHermano` int(11) NOT NULL,
`dni` varchar(9) NOT NULL,
`tipoCuota` int(11) NOT NULL,
`sexo` int(11) NOT NULL,
PRIMARY KEY (`codigo`),
KEY `sexo` (`sexo`),
KEY `pk_hno_cuota` (`tipoCuota`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `p_provincias` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(125) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

谢谢

Answer 1

父表和子表必须使用相同的存储引擎。

我注意到 hermanos 使用 InnoDB 而 p_provincias 使用 MyISAM。

有关详细信息，请参阅 here

Foreign keys definitions are subject to the following conditions:

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.