mysql error 1215 cannot add Foreign key Constraint

Question

Guys I’ve researched several other topics about this bug and none solved my problem so I’m posting here for you. Here’s the thing: I have a table called "members" that already has content and I’m creating a new table called membro_photos that has nothing in it. In this table I have the following columns: id, membro_id, photo. Both member and table id members are set to the following configuration: int-11 and both tables are in the Innodb engine. My problem is that when I try to create Foreign key in the table membro_photos it gives me error 1512. Why does this happen? and how to solve?

mysql> desc membros;
+---------------------+------------------+------+-----+---------+----------------
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| nome                | varchar(100)     | YES  |     | NULL    |                |
| nascimento          | varchar(10)      | YES  |     | NULL    |                |
| falecimento         | varchar(10)      | YES  |     | NULL    |                |

mysql> desc membro_images;
+---------------+--------------+------+-----+---------+-----------------------------+
| Field         | Type         | Null | Key | Default | Extra                       |
+---------------+--------------+------+-----+---------+-----------------------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment              |
| membro_id     | int(11)      | YES  | MUL | NULL    |                             |
| name          | varchar(255) | YES  |     | NULL    |                             |
| created_at    | timestamp    | YES  |     | NULL    | on update CURRENT_TIMESTAMP |
| updated_at    | timestamp    | YES  |     | NULL    | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+---------+-----------------------------+

Answer 1

The problem is in the field membro_id that accepts and has by default a value nulo, beyond the field id on the table members is configured not to accept negative numbers, type int(11) unsigned. How can I expect mysql to do this kind of restriction? Fields shall have the same data type/length.

I used the same fields of the tables you provided and did a quick test, passing as not null the value to membro_id.

create table membros (
  id int not null auto_increment comment 'pk_membros_id',
  nome varchar (100) not null,
  nascimento varchar (10),
  falecimento varchar (10),
  constraint pk_membros_id primary key (id)
  );

  insert into membros
    (nome,nascimento,falecimento)
  values
    ('Maria Joaquina Amaral Pereira', '1914-03-23', '1993-10-07');

 create table membros_fotos (
   id int not null auto_increment comment 'pk_membros_ft_id',
   membro_id int not null,
   name varchar (255),
   created_at timestamp not null default current_timestamp,
   update_at timestamp not null default current_timestamp on update current_timestamp,
   constraint pk_membros_ft_id primary key (id),
   constraint fk_membros_id_ft foreign key (membro_id) references membros(id)
 );

 insert into membros_fotos
   (membro_id,name)
 values (1,'images/membro_id_001.jpg');

See working on SQL Fiddle

Answer 2

John, To establish a Constraint between two tables, the fields MUST BE OF THE SAME TYPE AND SIZE. You need to check if in the member table there is the "membro_id".