Error creating Mysql foreign key in phpMyAdmin?

Question

Error creating Mysql foreign key in phpMyAdmin?

Asked 8 years, 6 months ago

Viewed 8,774 times

3

I’m having trouble creating foreign keys on mysql through the phpmyadmin.

I have these tables

CREATE TABLE `cidade` (
`idCidade` int PRIMARY KEY AUTO_INCREMENT,
`nome` varchar(25),
`Uf` CHAR(2)
);

CREATE TABLE `aeroporto` (
`idAeroporto` int PRIMARY KEY AUTO_INCREMENT,
`nome` varchar(100),
`endereco` varchar(200),
`idCidade` integer
);

But when I try to create a relationship

ALTER TABLE `usuario` ADD CONSTRAINT `fk_cidadeUsuario` FOREIGN KEY (`idCidade`)
REFERENCES `cidade` (`idCidade`);

I have the following error:

 #1005 - Can't create table `trab_tsnet_142`.`#sql-1a80_281` (errno: 150 "Foreign key constraint is incorrectly formed")

What might be going on?

(Obs: this is just one of the relationships that is giving error, I have the same problem in other relationships).

I edited the question because "phpmyadmin" is not a "database", read this to understand the differences: What is the difference between mysql and phpmyadmin?

– Guilherme Nascimento

2016/07/01 at 12:18
To help people who are making this mistake even though guys are the same. This can happen if at the time of creating the database or tables the default engine was left as Myisam, to solve just recreate the table and add below between the parentheses closes and the point and comma: "ENGINE=Innodb", innodb is the engine that makes the relationship between tables.

– Matheus Nunes Ismael

2018/02/15 at 01:15

1 answer

Browser other questions tagged mysql foreign-key

You are not signed in. Login or sign up in order to post.

by Simão Ítalo • **504** points · Answer 1 · 2016-05-22T21:35:49+00:00

Check that the fields you are trying to reference are exactly the same type and size. What may also be happening is that to add an eligible foreign key field by phpmyadmin, you will first have to turn it into index. After turning to index, look for the option Ver relações, in the table structure and add the Foreign key. I don’t know if it depends on the version of phpmyadmin, but the path is something like: clique no BD -> Tabela -> Estrutura -> Ver Relações (fica ao lado de "Visualização para impressão").