CODEWITHSUNDEEP
mysqlsql
user3953878
user3953878

Reputation:

Error Code: 1215. Cannot add foreign key constraint MySQL

Does anybody know why I have the error

Error Code: 1215. Cannot add foreign key constraint

When I try

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

On these tables:

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 ;

Thanks

Upvotes: 1

Views: 239

Answers (1)

Donal
Donal

Reputation: 32783

The parent and child tables must use the same storage engine.

I noticed that hermanos uses InnoDB and p_provincias uses MyISAM.

For more info, see 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.

Upvotes: 1

Related Questions