1

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

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yep, I copied the table from the Internet and I didn't see the properties.. Thanks!
@JoseGonzalez Glad to be of help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.